AutoFilter AND together two OR criteria?

[FlintstoneMan]

How do I AND together two OR criteria with AutoFilter in Excel 2002? Is there a way to use parentheses and then the Operator:xlAnd? I tried to but it resulted in an error. I am trying to see if a date range in fields 4 and 5 intersects with a date range in the variables. Below is a description of what I am trying to do. The "Operator:=xlAnd" is a placeholder for whatever I need to do to make it work.

With Worksheets("Project Data")
.Cells.AutoFilter Field:=4, Criteria1:=">=" & WeekBeginningDate, Operator:=xlOr
.Cells.AutoFilter Field:=4, Criteria1:="<=" & WeekEndingDate

Operator:=xlAnd

.Cells.AutoFilter Field:=5, Criteria1:=">=" & WeekBeginningDate, Operator:=xlOr
.Cells.AutoFilter Field:=5, Criteria1:="<=" & WeekEndingDate
End With

Thanks for your help.

 

[RoryA]

I'm not sure I see the problem. If you filter one column with an or then filter the other column with an or, the two are applied together so it is an And criteria.

 

[Jonmo1]

Welcome to the board...

You have both conditions of the OR on the same code line

Like

.Cells.AutoFilter Field:=4, Criteria1:=">=" & WeekBeginningDate, Operator:=xlOr, Criteria2:="<=" & WeekEndingDate


However, your logic is flawed.
EVERY SINGLE Possible date will be counted..

I think you want to use AND not OR.


Hope that helps.

 

[FlintstoneMan]

Thank you Jonmo1.

You were right about my logic, of course. It was getting all the rows.

Thanks.

 

[FlintstoneMan]

My question is now, of course, how to OR two ANDs.

These 2 auto filter lines work independently, but not when I put them together and add the xlOR. I was hoping that adding the xlOR after the first test would work because it is not between two criteria, but it doesn't.

I want to get a row if it passes either test. So, how do I OR the two ANDS? I feel like I'm missing something obvious. I've looked at help and Googled for the correct syntax, but I can't find a reference.

With Worksheets("Project Data")
.Cells.AutoFilter Field:=4, Criteria1:=">=" & WeekBeginningDate, Operator:=xlAnd, Criteria2:="<=" & WeekEndingDate, Operator:=xlOr

.Cells.AutoFilter Field:=5, Criteria1:=">=" & WeekBeginningDate, Operator:=xlAnd, Criteria2:="<=" & WeekEndingDate
End With

Thanks again for your help.

 

[FlintstoneMan]

Actually, it does work. Bad day, I guess.

 

[FlintstoneMan]

This code turns out not to work.
Fields 4 and 5 are the beginning and ending of a date range. I want to see if any day in that range falls into another date range represented by the variables WeekBeginningDate and WeekEndingDate.

If I test either field by itself, I get the correct answer. If I xlOr them together as follows, I get the wrong answer. Is this not the correct way to OR them together?

I appreciate any help in getting this to work.

With Worksheets("Project Data")
.Cells.AutoFilter Field:=4, Criteria1:=">=" & WeekBeginningDate, Operator:=xlAnd, Criteria2:="<=" & WeekEndingDate, Operator:=xlOr

.Cells.AutoFilter Field:=5, Criteria1:=">=" & WeekBeginningDate, Operator:=xlAnd, Criteria2:="<=" & WeekEndingDate
End With