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
– Nicholas Hunter, Commented May 27, 2021 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
– MonteCarlo, Commented May 27, 2021 at 18:33

Scott Craner · Accepted Answer · 2021-05-27 22:23:54Z

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, 2021 at 22:23

answered May 27, 2021 at 18:52

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MonteCarlo Over a year ago

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 <>?

Collectives™ on Stack Overflow

AdvancedFilter to exclude multiple criteria in Excel VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related