Trouble with Advanced Filter

[RunTime91]

Hello ~

Haven't experienced this before...

Below is basic Advanced Filter code using 2 criteria: "does not equal Lunch" and "does not equal Holiday"

When I run this code it won't filter for either of the two criteria

With Sheets("Sheet1")
    .Range("P2") = "<>" & "Lunch"
     .Range("P3") = "<>" & "Holiday"
    .Range("A1:J4705").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=.Range _
        ("M1:V3"), CopyToRange:=.Range("Y1:AC1"), Unique:=False
End With

However, if I change the CriteriaRange to the below code it will filter for: "Does not equal Lunch"

With Sheets("Sheet1")
    .Range("P2") = "<>" & "Lunch"
     '.Range("P3") = "<>" & "Holiday"
    .Range("A1:J4705").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=.Range _
        ("M1:V2"), CopyToRange:=.Range("Y1:AC1"), Unique:=False
End With

Likewise it I move "<>" & "Holiday" to .Range("P2") in the above code it will work. Thus I know there is nothing crazy with the criteria values or formatting causing the no filter in the first code.


Finally, if I add another header title for the column I'm filtering (as if I were filtering on a date range) and run the code below it will also work.

With Sheets("Sheet1")
    .Range("P2") = "<>" & "Lunch"
    .Range("Q2") = "<>" & "Holiday"
    .Range("A1:J4705").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=.Range _
        ("M1:W2"), CopyToRange:=.Range("Z1:AD1"), Unique:=False
    End With

So for whatever reason when I stack the criteria in P2 & P3 and use the CriteriaRange of ("M1:V3") it won't work at all.

Help?

 

[RunTime91]

Still can't get this to work and I have no idea what I'm doing wrong here... Can anyone see anything that might be causing the first window of code to not work?

Thanks...

 

[RunTime91]

Through continued research I learned that apparently you cannot use the "<>" operator in an "OR" Criteria format with advanced filter.
Thus, the reason the code in the 3rd window above works is because it is written in a an "AND" Criteria format.
I think we can label this one solved...

On to the next mystery...