Using an array to filter a table using more than two wildcard criteria: some values are missed

I need to filter a column in a large table using more than 2 criteria that include wildcards (*).
The table should show only rows where column A contains "VMI" and/or "PRIORITY" and/or "FAST" (not case sensitive).

I have borrowed the code from this answer (https://stackoverflow.com/a/34822944/21093323) to create an array using pattern matching:

Dim a As Long, aARRs As Variant, dVALs As Object, LR As Long

LR = Cells(Rows.Count, 1).End(xlUp).Row

Set dVALs = CreateObject("Scripting.Dictionary")
dVALs.CompareMode = vbTextCompare

With Worksheets("Sheet1")
    If .AutoFilterMode Then .AutoFilterMode = False
    With .Range("A2:E" & LR)
        ' Build a dictionary so the keys can be used as the array filter '
        aARRs = .Columns(1).Cells.Value2
        For a = LBound(aARRs, 1) + 1 To UBound(aARRs, 1)
            Select Case True
                Case aARRs(a, 1) Like "*FAST*"
                    dVALs.Add key:=aARRs(a, 1), Item:=aARRs(a, 1)
                Case aARRs(a, 1) Like "*VMI*"
                    dVALs.Add key:=aARRs(a, 1), Item:=aARRs(a, 1)
                Case aARRs(a, 1) Like "*PRIORITY*"
                    dVALs.Add key:=aARRs(a, 1), Item:=aARRs(a, 1)
                Case Else
                    ' No match, do nothing '
            End Select
        Next a

        ' Filter on column A if dictionary not empty '
        If CBool(dVALs.Count) Then _
            .AutoFilter Field:=1, Criteria1:=dVALs.Keys, Operator:=xlFilterValues

    End With
End With

dVALs.RemoveAll: Set dVALs = Nothing

Some of the rows that should have been included in the filter were not, and so I made a test case, but now I'm more confused than ever.

Results of filter tests using different cases in the "Select Case" code block
(In this test table, only the bottom two rows should be hidden by the filter)

I originally thought it was an issue with case sensitivity (even though from my research I'm lead to believe that "Like" is case in-sensitive), as you can see with the picture where I tested multiple case variations in column A and changed the "Select Case" code block to be:
all lower case ("fast"), ALL UPPER CASE ("FAST"), Pascal Case ("Fast")

I also tried using a helper column to convert all of the first column into upper case then using the array filter on the helper column, but that was still missing the top 3 rows out.

What makes me think it is not a case sensitivity issue is rows 3 and 12 in the test results image.
They are both identical, apart from row 3 starts with ABC123 instead of AB123.
I have no idea why row 12 works with the filter but row 3 doesn't.

If I add a new row with text "ABC123 FAST track" (which is the same text as row 3, just with capitalised "FAST") then row 3 DOES show after filtering...

This is leading me to think it might be an issue with how the array is being created, but I'm not sure, and I can't seem to figure out the behavior.

Solution

Filter on More than Two Wild Card Criteria

From Your Question

"some of the rows that should have been included in the filter were not"

"only the bottom two rows should be hidden by the filter"

The above are only your assumptions (expectations), but they are wrong.

"I'm lead to believe that "Like" is case in-sensitive"

You have been misled: a comparison using the Like operator is case-sensitive.
To enforce case-insensitivity, usually both strings are converted to upper- or lowercase, i.e.,
```
 Case UCase(aARRs(a, 1)) Like "*FAST*"
```
or
```
 Case LCase(aARRs(a, 1)) Like "*fast*"
```
Another way would be to compare the strings without wildcards using the Instr function with its last argument set to vbTextCompare.

"I have no idea why row 12 works with the filter but row 3 doesn't."

In row 3, the result of the comparison ("ABC123 fast track" Like "TRACK") is False because the comparison is case-sensitive. In row 12, the result of the comparison ("AB123 fast track" Like "TRACK") is also False for the same reason but in row 13, the result of the comparison ("AB123 FAST TRACK" like "TRACK") is True, so it is added to the dictionary. Case-insensitively (ignoring the case), rows 12 and 13 are the same. Since Autofilter is case-insensitive (ignores case), it also 'picks up' row 12.

From the Comments

"Ah I see, so Like can be case in-sensitive OR include wildcards, but not both at the same time?"

No, the comparison when using the Like operator is case sensitive and can include wildcards. If you use Option Compare Text (not recommended) at the top of the module, the comparison becomes case-insensitive.
The case sensitivity of the dictionary is determined by its CompareMode property. Setting it to vbTextCompare might yield fewer elements in the dictionary making the code more efficient but is not crucial, while the comparison of the strings is.

From Your Answer

Using On Error Resume Next is best avoided or at least, used on a smaller scope with a 'closing' On Error Goto 0.

A Different Approach (Instr)

Sub FilterInstr()

    ' Define constants.
    Const SHEET_NAME As String = "Sheet1"
    Const HEADER_ROW As Long = 2
    Const FILTER_COLUMN As Long = 1
    Const FILTER_LIST As String = "FaSt,vMI,PriORity"
    Const FILTER_LIST_DELIMITER As String = ","
    
    ' Reference the workbook.
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    ' If it isn't, reference it using its name or use 'ActiveWorkbook' instead.
    
    ' Reference the sheet and turn off auto-filtering.
    Dim ws As Worksheet: Set ws = wb.Sheets(SHEET_NAME)
    ws.AutoFilterMode = False
    
    ' Declare variables.
    Dim rg As Range, RowOffset As Long, RowsCount As Long
    
    ' Reference the range and retrieve its number of rows.
    With ws.Range("A1").CurrentRegion
        RowOffset = HEADER_ROW - 1
        RowsCount = .Rows.Count - RowOffset
        If RowsCount < 2 Then Exit Sub ' too few rows
        Set rg = .Resize(RowsCount).Offset(RowOffset)
    End With
    
    ' Return the values of the filter column in a 2D one-based array.
    Dim Data() As Variant: Data = rg.Columns(FILTER_COLUMN).Value
    
    ' Return the filter values in a 1D zero-based String array
    ' and retrieve its upper limit.
    Dim FilterValues() As String:
    FilterValues = Split(FILTER_LIST, FILTER_LIST_DELIMITER)
    Dim FiltersUpperLimit As Long: FiltersUpperLimit = UBound(FilterValues)
    
    ' Declare variables.
    Dim dict As Object, Value As Variant, Row As Long, f As Long
    Dim IsFound As Boolean
    
    ' Loop through the rows of the array and write
    ' all (unique) partially matching values to the keys of a dictionary.
    For Row = 2 To RowsCount
        Value = Data(Row, 1)
        If Not IsError(Value) Then
            For f = 0 To FiltersUpperLimit
                If InStr(1, Value, FilterValues(f), vbTextCompare) > 0 Then
                    If Not IsFound Then
                        Set dict = CreateObject("Scripting.Dictionary")
                        dict.CompareMode = vbTextCompare
                        IsFound = True
                    End If
                    dict(Value) = Empty
                    Exit For
                End If
            Next f
        End If
    Next Row
    
    ' Filter.
    If IsFound Then
        rg.AutoFilter FILTER_COLUMN, dict.Keys, xlFilterValues ' filter matches
    Else
        rg.AutoFilter FILTER_COLUMN, "<=>" ' filter none
    End If

End Sub