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

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.