I need to do an "and" filter on a date field in Excel 2003.

I have tried this and other variations.

Dim Current_Date, Previous_Date
Current_Date = Date
Previous_Date = Date - 14
'
Selection.AutoFilter Field:=1, Criteria1:="="Previous_Date,
Operator:=xlAnd _
, Criteria2:="=<"Current_Date

Any ideas how to get the dynamic dates to work in the Autofilter?

Thanks!

Hi Ron

Or use two cells on your worksheet with the dates

rng.AutoFilter Field:=4, Criteria1:="=" & CLng(Range("B1").Value), _
Operator:=xlAnd, Criteria2:="<=" & CLng(Range("C1").Value)

You can change the cell reference to Date and Date -14

See also this add-in
http://www.rondebruin.nl/easyfilter.htm



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ron Luzius" wrote in message ...
I need to do an "and" filter on a date field in Excel 2003.

I have tried this and other variations.

Dim Current_Date, Previous_Date
Current_Date = Date
Previous_Date = Date - 14
'
Selection.AutoFilter Field:=1, Criteria1:="="Previous_Date,
Operator:=xlAnd _
, Criteria2:="=<"Current_Date

Any ideas how to get the dynamic dates to work in the Autofilter?

Thanks!

You gave me the just hint I was looking for. I totally didn't think about
using the join "&" in the autofilter.
I did this and gave me exactly what I wanted to do. I was Sooo close!

Thanks for the extra set of eyes on this!

Dim Previous_Date, Current_Date
Current_Date = Date
Previous_Date = Current_Date - 5


Range("A1").Select
Selection.AutoFilter

Selection.AutoFilter Field:=1, Criteria1:="=" & Previous_Date,
Operator:=xlAnd _
, Criteria2:="<=" & Current_Date



"Ron de Bruin" wrote in message
...
Hi Ron

Or use two cells on your worksheet with the dates

rng.AutoFilter Field:=4, Criteria1:="=" & CLng(Range("B1").Value), _
Operator:=xlAnd, Criteria2:="<=" & CLng(Range("C1").Value)

You can change the cell reference to Date and Date -14

See also this add-in
http://www.rondebruin.nl/easyfilter.htm



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ron Luzius" wrote in message
...
I need to do an "and" filter on a date field in Excel 2003.

I have tried this and other variations.

Dim Current_Date, Previous_Date
Current_Date = Date
Previous_Date = Date - 14
'
Selection.AutoFilter Field:=1, Criteria1:="="Previous_Date,
Operator:=xlAnd _
, Criteria2:="=<"Current_Date

Any ideas how to get the dynamic dates to work in the Autofilter?

Thanks!