Problem with autofilter in VBA
 

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?

Problem with autofilter in VBA
 

this one always causes me grief too. My PC is set to
English dates ie DD/MM/YYYY
so your example is istFeb,1st Mar,1st Apr 1st May etc
in my code I need to enter the date American style ie
mm/dd/yy

so for dates 1st April I use the format$ to set the
text....

Sub TestFilter()
Dim filterdate As Date

filterdate = DateValue("1-Apr-2003")

With Range("A1:B1")
.AutoFilter
.AutoFilter Field:=2, _
Criteria1:="" & Format$(filterdate, "mm/dd/yy")
End With
End Sub


Patrrick 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,
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?




.