Dydnamic date in an autofilter
 

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!

Dydnamic date in an autofilter
 

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!

Dydnamic date in an autofilter
 

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!