Filtering based on date using VBA

[ajocius1]

Hi, I am using macro to filter list of data and would like to select all records with date less than today. I have used following VBA code to do that:

    ActiveSheet.Range("$A$5:$R$1000").AutoFilter Field:=18, Criteria1:="<" & Date, Operator:=xlAnd

This is one of few filters I apply, therefore Operator is AND.

I get empty list after running macro, but I can see that filter is applied. I do manually go into filter, it correctly points to Date Filter Before and has todays date. Once I go into filter manually and then go out without doing any changes, suddenly filtering is applied correctly and see the list of data as requested.

Why is my macro failing to achieve required result?

 

[gordsky]

replace your line

Criteria1:="<" & Date

with this

Criteria1:="<" & CDbl(Date)

 

[SQUIDD]

yeah

i was about to say, its not being recognised as a date.

i have used CLng(Date)

Dave

 

[gordsky]

Glad its working, please mark as resolved if complete

 

[SQUIDD]

gordsky

Not sure the original poster has seen or tried these yet.
Think you confused my comment as being from the OP

Dave