Excel AutoFilter to filter eliminate more than two criteria

[ErikJan]

AutoFilter can use two criteria.

What I like to do is filter OUT everything that contains certain strings...

With TWO criteria that is possible like this: Criteria1:="<>*String1*", Operator:=xlAnd, Criteria2:="<>*String2*"

Filtering with more criteria to SELECT the entries with certain strings is possible like this: Criteria1:=Array("String1", "String2", "String3", "String4"), Operator:=xlFilterValues

But how do I use more than two criteria to filter OUT the entries with these strings?? (I do not want to use the advanced filter).

[HansV]

You cannot do that, as you have found.

I would use a helper column. Let's say that the relevant column is column A, with data starting in row 2.
In a column adjacent to your data, enter a title such as Condition in row 1, and the following formula in row 2:

=OR(A2="String1",A2="String2",A2="String3",A2="String4")

and fill down to the last row with data. You can now simply filter on FALSE.

Another option would be:
Filter with Criteria1:=Array("String1", "String2", "String3", "String4"), Operator:=xlFilterValues to show only the rows with values that you want to exclude.
Use .SpecialCells(xlCellTypeVisible).EntireRow.Select to select the visible rows.
Turn off AutoFilter.
Hide the selected rows.

[ErikJan]

Thanks. Odd that you can use an array to match but not to exclude...
I might try to use a helper function then. The formula wouldn't work as I'll need a 'contains', not a match but I know how to do that ;-)

[Rudi]

Another option based on one of Hans's suggestions above:

List all the values to filter out (remove) in the list on sheet 2

Code: Select all

Sub FilterWithArray()
Dim rgFilterValues As Variant
    rgFilterValues = Sheets(2).Range("A1:A10") 'Edit to accommodate the filter range
    With Sheets(1)
        .Range("A1").AutoFilter Field:=1, Criteria1:=Application.Transpose(rgFilterValues), Operator:=xlFilterValues
        .Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp).Offset(1)).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .AutoFilterMode = False
    End With
End Sub

[Rudi]

Here is an example I had on record based on the "helper column" option...

Code: Select all

Sub DelFilterExcluding()
    With Sheets(1)
        .Range("A1").EntireColumn.Insert
        'List of ALL items to EXCLUDE...
        .Range(.Cells(2, "A"), .Cells(.Rows.Count, "B").End(xlUp).Offset(0, -1)).FormulaR1C1 = _
            "=OR(RC[1]=""Item1"", RC[1]=""Item2"", RC[1]=""Item3"")"
        .Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:="TRUE"
        With .AutoFilter.Range
            On Error Resume Next
            .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
            On Error GoTo 0
        End With
        .AutoFilterMode = False
        .Range("A1").EntireColumn.Delete
    End With
End Sub