CODEWITHSUNDEEP
excelvbaexcel-formulaexcel-2019
MonteCarlo
MonteCarlo

Reputation: 26

AdvancedFilter to exclude multiple criteria in Excel VBA

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

enter image description here

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

enter image description here

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?

Upvotes: 0

Views: 1063

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

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:

enter image description here

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

enter image description here

Upvotes: 2

Related Questions