Recording a filter which then doesn't work.
 

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

Recording a filter which then doesn't work.
 

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.

--
Regards,
Tom Ogilvy


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

Recording a filter which then doesn't work.
 

Tom,

Cool - that works perfectly.

I learned something - my day has meaning! :-)

Thanks very much

Pete

"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy


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

Recording a filter which then doesn't work.
 

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.)


Tom Ogilvy wrote:
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.

--
Regards,
Tom Ogilvy


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

Recording a filter which then doesn't work.
 

Andrew,

Thanks for this! :-)

Regards

Pete



"Andrew Taylor" wrote:

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.)


Tom Ogilvy wrote:
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.

--
Regards,
Tom Ogilvy


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