AdvancedFilter to exclude multiple criteria in Excel VBA

Question

I've been trying to use VBA's advanced filter method to exclude a set of criteria. Here is a simplified example.

My criteria are stored in the worksheet "Criteria", as such.

I now want to filter the data, such that any entry containing the name "Joe" OR the job "Pilot" are excluded. I have tried the following:

Sub filter_data()
 With Worksheets("Data")
  .Activate
  .Range("A1:C10").AdvancedFilter _
  Action:= xlFilterInPlace, _
  CriteriaRange:= Worksheets("Criteria").Range("A1:C3")
 End With
End Sub

I checked if my code filters the data if I don't use the <> operator. This worked like a charm and included only entries containing "Joe" OR "Pilot". However, excluding the said entries does not work. Can anybody help?

Answer 1

Since you are looking for the negative you want AND not Or.

Then in A2 put: <>Joe

Then in C2 put: <>Pilot

So it looks like this:

Then change the reference to only the two rows:

Sub filter_data()

    With Worksheets("Data")
        .Range("A1:C10").AdvancedFilter _
        Action:=xlFilterInPlace, _
        CriteriaRange:=Worksheets("Criteria").Range("A1:C2")
    End With
End Sub