Multiple filter criteria using VBA

[Thundercats]

Hi,
I am kind of new to VBA. I used ChatGPT to generate most of my VBA code. I am trying to filter a column for multiple values to exclude certain items that begin with certain characters like RX, SV, IT etc. I have tried using for-next loops and also using an array but I am able to only exclude more than one criteria at time. If it works for one criteria, then it includes all others. My headers with the filters start on A2 and go all the way to AB2. I somehow think it does not like the <> in the code below, but I could be wrong. Appreciate any help.

Sub ApplyMultipleFilters()
Dim ws As Worksheet
Dim lastRow As Long
Dim filterRange As Range
Dim visibleRange As Range
Dim criteriaArray As Variant
Dim FilterArray As Variant
Dim i As Integer
Set ws = ThisWorkbook.Sheets("Sheet 1") ' Adjust the sheet name as necessary

' Clear existing filters
ws.AutoFilterMode = False
With ws
' Find the last filled row in column A
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

' Define the range starting from row 2
Set filterRange = ws.Range("A2:AB2" & lastRow)

' Apply filter
filterRange.AutoFilter

' Apply filter to exclude "RX*" pattern
'filterRange.AutoFilter Field:=1, Criteria1:="<>*RX*", Operator:=xlAnd

' Reapply the range to only the visible cells and apply the next filter
'Set visibleRange = filterRange.SpecialCells(xlCellTypeVisible)
'visibleRange.AutoFilter Field:=1, Criteria1:="<>*SV*"

' Reapply the range to only the visible cells and apply the next filter
'Set filterRange = ws.Range("A2:AB" & lastRow).SpecialCells(xlCellTypeVisible)
'filterRange.AutoFilter Field:=1, Criteria1:="<>*IT*"

' Reapply the range to only the visible cells and apply the next filter
'Set filterRange = ws.Range("A2:AB" & lastRow).SpecialCells(xlCellTypeVisible)
' filterRange.AutoFilter Field:=1, Criteria1:="<>*IV*"

' Reapply the range to only the visible cells and apply the next filter
'Set filterRange = ws.Range("A2:AB" & lastRow).SpecialCells(xlCellTypeVisible)
'filterRange.AutoFilter Field:=1, Criteria1:="<>*LB*"

'Define the criteria array for exclusion patterns
criteriaArray = Array("<>*RX*", "<>*SV*", "<>*IT*", "<>*IV*", "<>*LB*")

'Loop through each exclusion pattern and apply the filter
For i = LBound(criteriaArray) To UBound(criteriaArray)
Set visibleRange = filterRange.SpecialCells(xlCellTypeVisible)
visibleRange.AutoFilter Field:=1, Criteria1:= criteriaArray(i)
Next i
End With
End Sub

 

[John_w]

You can do it with Advanced Filter, like this:

Public Sub Filter_Multiple_Criteria()

    With Worksheets("Sheet1")
        If .FilterMode Then .ShowAllData
        .Range("AD2:AF2").Value = .Range("A2").Value
        .Range("AD3:AF3").Value = Array("<>RX*", "<>SV*", "<>IT*")
        .Range("A2").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=.Range("AD2:AF3"), Unique:=False
    End With
    
End Sub

 

[Thundercats]

You can do it with Advanced Filter, like this:

Public Sub Filter_Multiple_Criteria()

    With Worksheets("Sheet1")
        If .FilterMode Then .ShowAllData
        .Range("AD2:AF2").Value = .Range("A2").Value
        .Range("AD3:AF3").Value = Array("<>RX*", "<>SV*", "<>IT*")
        .Range("A2").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=.Range("AD2:AF3"), Unique:=False
    End With
   
End Sub

Thank you for the code. I will try this in a day or so and let you know if it worked. Hoping it does.