excel - Can Advanced Filter criteria be in the VBA rather than a range?

Question

After trying in vain to set more than 2 criteria in a normal AutoFilter fashion via VBA, I have come to learn that it must be done via advanced filter.

offending example:

Worksheets(1).Range("A1").AutoFilter Field:=ColNum, Criteria1:="A*", Operator:=xlOr, Criteria2:="B*", Operator:=xlOr, Criteria3:="C*"

I am hoping to pass the criteria through to a function (rather than a macro) from a PowerShell script. I have it all working fine and as expected for 1 criteria, but now I'd like 3.

I suppose I could instead write a macro to insert a new sheet, write in the criteria then filter on that new range but I'd rather check the preferred way first.

Answer 1

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.

??????

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:

??????

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.