AutoFilter, using "Today's Date"

[Guest]

Kia Ora from NZ

I would like a macro that filters out rows depending on todays date - i.e.
one that will show all the rows, if they have the present date in a
particular column.

I've managed this much on my own ....

Sub ShowAllOverdueFiles()
'
' ShowAllOverdueFiles Macro
' Macro recorded 29/01/2006 by Mike
'

'

Selection.AutoFilter Field:=6, Criteria1:="<" & Range("V1").Value,
Operator:=xlAnd

Range("a2").Select

End Sub


However, when I run that, all my rows disappear. BUT, if I go to the Custom
Autofilter option, the autofilter is there for today's date, and it works
when you press OK.

I guess I just need to know how the macro can execute itself, right through
to the end!

Many thanks in advance!


Mike

 

[Norman Jones]

Hi Kia Ora,

Try changing:

Range("V1").Value

to

CLng(Range("V1").Value)

 

[Norman Jones]

Hi Mike,

Forgive my ignorance of Maori salutations!

 

[Guest]

thanks NJ

that's just beautiful!

i've been struggling with this all day!!

What exactly does the "CLng" do?? or do I need not know!??



cheers

mike

 

[Norman Jones]

Hi Mike,

The CLng conversion function converts the value to an integer. Using non US
style dates, I need to pass dates to the autofilter in the form of long
integers.

When the same operation is performed manually, Excel performs this operation
implicitly. VBA is, however, US-centric in its interpretation and treatment
of dates.