79631813

Date: 2025-05-21 10:49:07
Score: 1
Natty:
Report link

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
Reasons:
  • Blacklisted phrase (1): anyone knows
  • Long answer (-1):
  • Has code block (-0.5):
  • Self-answer (0.5):
  • Low reputation (1):
Posted by: Waffly