Recording a filter which then doesn't work.

[Guest]

Good afternoon, all

I recorded the following action, to filter my databae to show rows whe the
Date field contained a date between 21/10/05 and 20/11/05.

Sub Macro7()
Selection.AutoFilter Field:=1, Criteria1:=">=21/10/2005",
Operator:=xlAnd _
, Criteria2:="<=20/11/2005"
End Sub


As I recorded, the filter operation worked OK, but when I remove the filter,
then run the macro again, the database filters out ALL rows so that only the
firld headers are displayed.
Can anyone suggest why?

Thanks in advance

pete

 

[Tom Ogilvy]

Generally this is because VBA uses US English formatted dates (mdy) and your
dates are in dmy. Try switching them to mdy and see if it works.

 

[Guest]

Tom,

Cool - that works perfectly.

I learned something - my day has meaning!

Thanks very much

Pete

 

[Andrew Taylor]

It does seem to be related to that fact that the filter is on dates -
similar
code with plain numeric data works fine. OTOH the filter does seem to
be
getting set correctly: if after running the code I select the drop-down
to view
the "Custom" setting and hit enter (with no changes), it then displays
the filtered rows correctly.

(This is with Excel 2000 and UK settings.)

 

[Guest]

Andrew,

Thanks for this!

Regards

Pete

It does seem to be related to that fact that the filter is on dates -
similar
code with plain numeric data works fine. OTOH the filter does seem to
be
getting set correctly: if after running the code I select the drop-down
to view
the "Custom" setting and hit enter (with no changes), it then displays
the filtered rows correctly.

(This is with Excel 2000 and UK settings.)