CODEWITHSUNDEEP
excelvbafilter
PedroB
PedroB

Reputation: 77

VBA - How to use multiple criteria from a range with wildcards, for autofilter

I need to filter some values of a sheet, and these values that I need to filter is in a table, but this one is dynamic, sometimes there's only one value, or two,three... Ex: Today i'll be ' ABC - CBA - DDA ' , and tomorrow ' DNC - AB '. Here is my code of filters that I also need to use with this:

        totrow = Range("A1").End(xlDown).Row
        Range(Cells(1, 1), Cells(1, 9)).AutoFilter
        Range(Cells(1, 6), Cells(totrow, 6)).Select
        
        ActiveSheet.Range(Cells(1, 6), Cells(totrow, 6)).AutoFilter Field:=6, Criteria1:="816"
        ActiveSheet.Range(Cells(1, 2), Cells(totrow, 2)).AutoFilter Field:=2, Criteria1:="RWK"
        
        '**here goes the other filters**

Upvotes: 0

Views: 1135

Answers (1)

Wizhi
Wizhi

Reputation: 6549

Suppose we have set of data that we want to filter:

enter image description here

and we also have a column/range with values we want to use as filter (Column N).

If the values always are the same, we can use in our filtering a hard coded array:

.AutoFilter Field:=8, Criteria1:=Array("ABC - CBA - DDA", "DNC - AB")

However, if the values or range are changing, we need to create a dynamic array that loops through our range and store the values as an array.

'Create an array to store the values to filter on
ReDim aCritVal(0 To lrow_crit) As Variant 'Create an array variable "aCritVal" to store the Criteria Value we want to use as filter
i = 0 'Array index starts at 0
For Each CellVal In ws.Range(ws.Cells(3, "N"), ws.Cells(lrow_crit, "N")) 'Range to loop through where Criteria Values exists
    'Debug.Print CellVal.Value 'Checks the cell value that will be stored in Array: aCritVal
    aCritVal(i) = CellVal.Value 'Store the value in array
    i = i + 1 'Add one to next loop
Next CellVal
ReDim Preserve aCritVal(i - 1) 'Resize the array to only contain the values we need

Then we can substitute the array from:

Criteria1:=Array("ABC - CBA - DDA", "DNC - AB") -> Criteria1:=aCritVal

We can also add additional filtering steps to our filter section.

With the code below, where we use 3 filtering (1 array + 2 additional criteria's) we will get: enter image description here

Code:

Option Explicit

Sub auto_filter_from_critera_range()

Dim lrow_filter As Long
Dim lcol_filter As Long
Dim lrow_crit As Long
Dim i As Long
Dim CellVal As Variant
Dim MyRangeFilter As Range


Dim ws As Worksheet
Set ws = ActiveWorkbook.Worksheets("Sheet1") 'Set the worksheet name

lrow_crit = ws.Cells(Rows.Count, "N").End(xlUp).Row 'Find last row in criteria table, in Sheet1
lrow_filter = ws.Cells(Rows.Count, "A").End(xlUp).Row 'Find last row for filter, in Sheet1
lcol_filter = ws.Cells(1, Columns.Count).End(xlToLeft).Column 'Find last column for filter, Sheet1


'Create an array to store the values to filter on
ReDim aCritVal(0 To lrow_crit) As Variant 'Create an array variable "aCritVal" to store the Criteria Value we want to use as filter
i = 0 'Array index starts at 1
For Each CellVal In ws.Range(ws.Cells(3, "N"), ws.Cells(lrow_crit, "N")) 'Range to loop through where Criteria Values exists
    'Debug.Print CellVal.Value 'Checks the cell value that will be stored in Array: aCritVal
    aCritVal(i) = CellVal.Value 'Store the value in array
    i = i + 1 'Add one to next loop
Next CellVal
ReDim Preserve aCritVal(i - 1) 'Resize the array to only contain the values we need


If ws.AutoFilterMode Then ws.AutoFilterMode = False 'If autofilter exists then remove autofilter
Set MyRangeFilter = ws.Range(ws.Cells(1, 1), ws.Cells(lrow_filter, lcol_filter)) 'Define the range to apply autofilter
' MyRangeFilter.Select 'Select the range to filter, might be needed in some instances
                
With MyRangeFilter
    '.AutoFilter Field:=8, Criteria1:=Array("ABC - CBA - DDA", "DNC - AB"), Operator:=xlFilterValues '"Normal" array that can be used if we have fixed criteria values
    .AutoFilter Field:=8, Criteria1:=aCritVal, Operator:=xlFilterValues 'Our array value that we crated in the above section, that will be used as filter
    .AutoFilter Field:=6, Criteria1:="816" '"Regular" critera value
    .AutoFilter Field:=2, Criteria1:="RWK"
End With

End Sub

The criteria range should only include the values for the criteria we want to filter. Please notice that I have used header for the filter criteria at row 2, as I use lcol_filter to find the last column to apply filter, therefore I put the filter table on row 2 and the first value will be in row 3.

For Each CellVal In ws.Range(ws.Cells(3, "N"), ws.Cells(lrow_crit, "N"))
    '.......
Next CellVal

For wildcard in the array:

Criteria1:=Array("*CBA*", "*AB*")

Change the part of the code where we store the values and add asterix: aCritVal(i) = CellVal.Value -> aCritVal(i) = "*" & CellVal.Value & "*"

Upvotes: 1

Related Questions