excel - VBA: Autofilter for Partial Array strings

Question

I need to filter a column for the array I've made. I'm trying to use Cells.AutoFilter Field:=14, Criteria1:=stringArray, Operator:= but I don't know what the operator should be.

An example of my issue is that something in my Array might be "Ta" when what's in the column I'm autofiltering is actually "Tawm". I'm thinking something like Operator:=xlContains but that's a no-go.

I just want it to be like I'm typing in "Ta" and then selecting all the options which the autofilter finds.

I've tried to add "*" to each entry in the array with the following code but it doesn't seem to help:

Dim stringArray As Variant
Dim tempMfr As String
Dim temp2Mfr As String
Dim t As Variant

tempMfr = xCell & "*"
temp2Mfr = xCell.Offset(0, 2) 'this cell may have multiple entries such as "a, b, c"
stringArray = Split(temp2Mfr, ", ")
For Each t In stringArray
t = t & "*"

Cells.AutoFilter Field:=14, Criteria:=stringArray, Operator:=xlFilterValues

---

Is there a better way to do this?

Thanks.

Answer 1

You cannot use wildcards in more than two criteria of the AutoFilter Method and then it must be like either,

.AutoFilter Field:=1, Criteria1:="=abc*", Operator:=xlAnd, Criteria2:="=def*"
' or,
.AutoFilter Field:=1, Criteria1:="=abc*", Operator:=xlOr, Criteria2:="=def*"

After splitting the string into a variant array, simply loop through the elements of the array and append an asterisk (e.g. Chr(42)) to each element.

Sub Macro3()
    Dim v As Long, vFilters As Variant, sFilters As String
    Dim xCell As Range

    With Sheets("Sheet1")
        Set xCell = .Range("ZZ99")
        sFilters = xCell.Offset(0, 2) 'this cell may have multiple entries such as "a, b, c"
        vFilters = Split(sFilters, ", ")
        With .Cells(1, 1).CurrentRegion
            For v = LBound(vFilters) To LBound(vFilters)
                .Cells.AutoFilter Field:=14, Criteria:=vFilters(v) & Chr(42) 'begins with filter
                'move down a row to save the header and resize -1 row
                With .Resize(.Rows.Count - 1, .Columns.Count).offswet(1, 0)
                    'check if there is anything visible
                    If CBool(Application.Subtotal(103, .Columns(14))) Then
                        'do something with
                        '.Cells.SpecialCells (xlCellTypeVisible)
                    End If
                End With
            Next v
        End With
    End With

End Sub

If you need more than two 'wildcarded' criteria at once, a significantly larger amount of coding can be used to build an array to pass into the filter and then use the Operator:=xlFilterValues option.