Dydnamic date in an autofilter

[Ron Luzius]

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!

 

[Ron de Bruin]

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

 

[Ron Luzius]

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