Using the Today() or Date() functions within the Excel vba autofilter?

[knifey]

Hi,
Can anyone advise on how to enter date() or today() criteria into the Excel vba filter? I'm trying to exclude rows that have been added within the last 3 days.

This command runs without error but does nothing:

wb1.Sheets("Master").Range("A2:AS2").AutoFilter Field:=dateAddedToMasterCol, Criteria1:="<>date()" _
Operator:=xlAnd, Criteria2:="<>date()-1" , etc., etc.

Any help would be much appreciated.

K

 

[PHV]

What about this ?
wb1.Sheets("Master").Range("A2:AS2").AutoFilter Field:=dateAddedToMasterCol, Criteria1:="<" & Date - 2

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

hi,

    wb1.Sheets("Master").Range("A2").CurrentRegion.AutoFilter Field:=dateAddedToMasterCol, Criteria1:= _
        "<>" & Date, Operator:=xlAnd, Criteria2:="<>" & Date - 1

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[knifey]

Hi PHV/Skip,
This works great for US dates, but I'm in the UK.

I've tried reformatting the date but it's throwing up a type mismatch error. Any ideas?

.Range("A2:AS2").AutoFilter Field:=DateAddedToMasterCol, Criteria1:="<" & Format(Date, "dd/mm/yyyy") - 2

Thanks,
K

 

[knifey]

Found it, convert date to long:

http://www.ozgrid.com/VBA/autofilter-vba-dates.htm