AutoFilter two independent columns with criteria and display all rows contains that criteria-CodePudding

As you see on the above picture, I need to use AutoFilter to show rows contains specific value e.g 102.
With Excel interface , I cannot use the criteria value 102 on Columns “B” & “C” on the same time.
I want to maintain the sort and structure of my dataset.
As a workaround, is it possible to show rows contains value 102 on Columns “B” & “C” and hide the other rows in between.
In advance I am grateful for all your help.

Sub Filter_criteria()
 
   Dim ws As Worksheet
   Set ws = ThisWorkbook.ActiveSheet
 
   Dim rng As Range
   Set rng = ws.Range("A2:R" & ws.Cells(Rows.Count, "A").End(xlUp).Row)
 
   If Not ws.AutoFilterMode Then rng.AutoFilter       'Set AutoFilter if not already set
 
  rng.AutoFilter Field:=2, Criteria1:="*102*", Operator:=xlAnd
 
End Sub

CodePudding user response：

Please, test the next adapted code. It firstly, apply a filter on the second column, then unhide rows if the third column contains criteria:

Sub Filter_criteria()
    Dim ws As Worksheet: Set ws = ThisWorkbook.ActiveSheet
    Const crit As String = "*102*"
         
    If ws.AutoFilterMode Then ws.AutoFilterMode = False      
    Dim rng As Range: Set rng = ws.Range("A1:R" & ws.cells(rows.count, "A").End(xlUp).row)
          
    'place the first filter in second column:
    rng.AutoFilter field:=2, Criteria1:=crit, Operator:=xlFilterValues
         
    'unhide according to the third column, by iteration:
    For i = 1 To rng.rows.count
        If rng.cells(i, 3).Value Like crit Then rng.rows(i).Hidden = False
    Next i
End Sub

A second version follows somehow BibBen's suggestion. The code builds an array as result of B:B and C:C concatenation and drop its content after the last column (after R:R), then filter by it and clear at the end:

Sub evaluateConcat()
   Dim ws As Worksheet, lastR As Long, lastCol As Long, rng As Range, arr
   Const crit As String = "*102*"
   
   Set ws = ActiveSheet
   If ws.AutoFilterMode Then ws.AutoFilterMode = False
   lastR = ws.Range("A" & ws.rows.count).End(xlUp).row
   lastCol = ws.cells(1, ws.Columns.count).End(xlToLeft).Column
   Set rng = ws.Range("A1:S" & lastR)
   
   'create an array as concatenation between columns B:B and C:C
   arr = Evaluate(ws.Range("B2:B" & lastR).Address & "&" & ws.Range("C2:C" & lastR).Address)

   With ws.cells(1, lastCol   1)
        .Value = "ConcCol"      'header
        .Offset(1).Resize(UBound(arr), 1).Value = arr 'drop the array content after the last column
   End With
   
   rng.AutoFilter field:=lastCol   1, Criteria1:=crit, Operator:=xlFilterValues  'filter by the above built column

   ws.Columns(lastCol   1).ClearContents 'clear the content of the added column
End Sub

Edited:

A third version will iterate between the two columns keeping criteria, placed in an array and build a Union range (of not matching criteria) to be finally hidden:

    Sub FilterByTwoCols()
      Dim ws As Worksheet, lastR As Long, arr, i As Long, HdRng As Range
      Const crit As String = "*102*"
      
      Set ws = ThisWorkbook.ActiveSheet
      ws.UsedRange.EntireRow.Hidden = False 'make all rows visible
      lastR = ws.Range("A" & ws.rows.count).End(xlUp).row
      
      arr = ws.Range("B2:C" & lastR).Value2 'place the relevant columns in an array for faster iteration
      For i = 1 To UBound(arr)
            If Not arr(i, 1) & arr(i, 2) Like crit Then
                addToRange HdRng, ws.Range("A" & i   1) 'make  a Union range of the rows NOT matching criteria...
            End If
      Next i
      If Not HdRng Is Nothing Then HdRng.EntireRow.Hidden = True 'hide not matching criteria rows.
End Sub

Private Sub addToRange(rngU As Range, rng As Range)
    If rngU Is Nothing Then
        Set rngU = rng
    Else
        Set rngU = Union(rngU, rng)
    End If
End Sub