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?

Your filter will INCLUDE all entries where Name is not Joe OR Job is not pilot. Is that what you want? If not, you need to express your filter criteria in terms of which rows you want to INCLUDE. — Nicholas Hunter, May 27 '21 at 18:21
Yes, that is what I need. I want to include every entry that does not contain "Joe" OR "Pilot". Ultimately, I should be left with 5 entries and none of them contain the name "Joe" OR the job "Pilot". However, running my code like this filters nothing. — MonteCarlo, May 27 '21 at 18:33

Scott Craner · Accepted Answer · 2021-05-27T22:23:54.683

2

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

edited May 27 '21 at 22:23

answered May 27 '21 at 18:52

Scott Craner

148,073
10
49
81

Thank you, that is what I was looking for. In my real data set, however; I also have cases where I want to exclude entries based on criteria 1 AND criteria 2. In this example, how do I exclude only entries where the name is "Joe" AND the job is "Doctor". Hence, only the entry in row 3 should be excluded. Is there also a clear differentiation between AND/ OR in the case where I use ```<>```? – MonteCarlo May 28 '21 at 06:15

AdvancedFilter to exclude multiple criteria in Excel VBA

1 Answers1