VBA autofilter bug??

[godgo]

Can anyone help me explain why the following code does not work. It appears
to be a bug in Excel 2003.

This is a simple spreadsheet that contains a command button that calls a
small piece of vba code to do an autofilter on a range of dates.

Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:=">1/01/2003", Operator:=xlAnd,
Criteria2:="<31/03/2003"

When the line is executed, it returns no data, however if you manually go
into the autofilter drop down the values are there and a simple OK to the
dialogue box will return the correct results.

The spreadsheet contains the following

Record Number Date
1 2/01/2003
2 1/02/2003
3 1/03/2003
4 1/04/2003
5 1/04/2003
6 1/05/2003


The code above should return record numbers 1 thru 3, but it displays no
lines of code util you go in manually


Am I missing something?

 

[Patrick Molloy]

try

Criteria1:=">1/1/2003", Operator:=xlAnd,
Criteria2:="<3/31/2003"


I have found that, even with my set to UK dates
(dd/mm/yy) that the filter needs US staye dates (
mm/dd/yy)


Patrick Molloy
Microsoft Excel MVP

-----Original Message-----
Can anyone help me explain why the following code does not work. It appears
to be a bug in Excel 2003.

This is a simple spreadsheet that contains a command button that calls a
small piece of vba code to do an autofilter on a range of dates.

Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2,

Criteria1:=">1/01/2003", Operator:=xlAnd,

 

[Tom Ogilvy]

Selection.AutoFilter Field:=2, Criteria1:=">" & DateSerial(2003,1,1),
Operator:=xlAnd,
Criteria2:="<" & DateSerial(2003,3,31)

Excel VBA generally works in US date formats. The interface between the
autofilter criteria and VBA, when dealing with dates, is tenuous, but
usually using dateserials will work.

 

[godgo]

Thanks Patrick. That did the trick.

regards
dion

 

[Tom Ogilvy]

godgo emailed me that this didn't work.

It works fine for me and I have others in regions with dd/mm/yyyy format
tell me it works for them.

If it didn't work for him/her I suspect there is some reason related to the
particulars of the data in their sheet.

 

[VEspanha]

Try it:

.AutoFilter Field:=3, Criteria1:=">=" & Format(Date, "mm/dd/yy") :

 

[Bob Phillips]

I can only see this reply, so I am guessing at the probem, but I know a
probem in Autofilter with dates.

Try this

AutoFilter Field:=3, Criteria1:=">=" & Format(Date, "mm/dd/yy"), _
Operator:=xlAnd, _
Criteria1:="<=" & Format(Date, "mm/dd/yy")

--

HTH

RP
(remove nothere from the email address if mailing direct)