I have fixed the filter... but I still don't understand why it was behaving the way it was before.
I would still be curious to see if anyone knows why the filter wasn't working as intended.
I added:
Option Compare Text
to the top of the module.
I changed the range in the "With" block to: .Range("A1:B" & LR)
,
as .Range("A2:B" & LR)
was skipping row 2.
I added: On Error Resume Next
before the "Select Case" block to ignore any duplicates.
Full working code below in case anyone stumbles upon this and wants to use it:
Option Explicit
Option Compare Text
Sub multiWildcardFilter()
Dim a As Long, aARRs As Variant, dVALs As Object, LR As Long
LR = Worksheets("Sheet1").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("A1:B" & 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)
On Error Resume Next
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
End Sub