To filter on multiple wildcards, create a variant array of wildcard matches and then use the array of full values with the standard AutoFilter method. You can minimize the array by putting a dictionary object to use with its unique index property.
Consider the following sample data.
![autofilter_multi_wilcard]()
Run this code.
Sub multiWildcards()
Dim v As Long, vVALs As Variant, dVALs As Object
Dim colNum As Long
Set dVALs = CreateObject("Scripting.Dictionary")
dVALs.comparemode = vbTextCompare
colNum = 2 'column B
With Worksheets(1)
If .AutoFilterMode Then .AutoFilterMode = False
With .Cells(1, 1).CurrentRegion
vVALs = .Columns(colNum).Cells.Value2
For v = LBound(vVALs, 1) To UBound(vVALs, 1)
If Not dVALs.exists(vVALs(v, 1)) Then
Select Case UCase(Left(vVALs(v, 1), 1))
Case "A", "B", "C"
dVALs.Add Key:=vVALs(v, 1), Item:=vVALs(v, 1)
Case Else
'do nothing
End Select
End If
Next v
If CBool(dVALs.Count) Then
'populated the dictionary; now use the keys
.AutoFilter Field:=colNum, Criteria1:=dVALs.keys, Operator:=xlFilterValues
Else
Debug.Print "Nothing to filter on; dictionary is empty"
End If
'.CurrentRegion is now filtered on A*, B*, C* in column B
'do something with it
End With
End With
dVALs.RemoveAll: Set dVALs = Nothing
End Sub
Results should be:
![autofilter_multi_wilcard_results]()
These results can be duplicated with many other wildcard scenarios. The Select Case statement is ideal as it supports the Like keyword for building your collection of matches. By starting with a value dump into a regular variant array, cycling through even large rows of data can be done quickly.
