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

Hi Kia Ora,

Try changing:

Range("V1").Value

to

CLng(Range("V1").Value)


---
Regards,
Norman



"Blobbies" wrote in message
...
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

Hi Mike,

Forgive my ignorance of Maori salutations!


---
Regards,
Norman

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" wrote:

Hi Kia Ora,

Try changing:

Range("V1").Value

to

CLng(Range("V1").Value)


---
Regards,
Norman



"Blobbies" wrote in message
...
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

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.

--
---
Regards,
Norman



"Blobbies" wrote in message
...
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" wrote:

Hi Kia Ora,

Try changing:

Range("V1").Value

to

CLng(Range("V1").Value)


---
Regards,
Norman



"Blobbies" wrote in message
...
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