Autofilter vba dont show filtered data

[Guest]

When i run my autofilter code, range with autofilter is empty ( except autofilter columns ). Then if i select "(Custom...)" in autofilter list i can see correct dates in criteria fields and after i cick OK, Autofilter filter and show correct rows.

my autofilter code;

Range("a1").AutoFilter Field:=4, Criteria1:=">=27.5.2004" , Operator:=xlAnd, Criteria2:="<=3.6.2004"

I'm using Excel 97 and Windows XP Pro

 

[Tom Ogilvy]

Try

Range("a1").AutoFilter Field:=4, Criteria1:=">=" & DateSerial(2005,5,27), _
Operator:=xlAnd, Criteria2:="<=" & DateSerial(2004,6,3)

--
Regards,
Tom Ogilvy

When i run my autofilter code, range with autofilter is empty ( except

autofilter columns ). Then if i select "(Custom...)" in autofilter list i
can see correct dates in criteria fields and after i cick OK, Autofilter
filter and show correct rows.

my autofilter code;

Range("a1").AutoFilter Field:=4, Criteria1:=">=27.5.2004" ,

Operator:=xlAnd, Criteria2:="<=3.6.2004"

 

[Guest]

Thanks Tom, but it didnt work

Try

Range("a1").AutoFilter Field:=4, Criteria1:=">=" & DateSerial(2005,5,27), _
Operator:=xlAnd, Criteria2:="<=" & DateSerial(2004,6,3)

--
Regards,
Tom Ogilvy


autofilter columns ). Then if i select "(Custom...)" in autofilter list i
can see correct dates in criteria fields and after i cick OK, Autofilter
filter and show correct rows.
Operator:=xlAnd, Criteria2:="<=3.6.2004"

 

[Tom Ogilvy]

Did you correct my typo. I had 2005 in the first critera - change it to
2004 (nothing would match the criteria I set).


Other than that, try turning on the macro recorder and recording while you
do it manually, but I have good success with what I suggested.

 

[Guest]

Yep. i fixed typo. There must be something wrong with the Cell Format and date column, because if i try to autofilter other column that contains numbers only, code works.

 

[Tom Ogilvy]

There is by definition. The autofilter has problems with dates when set
from VBA if the date format of the region (of that verion) uses non US
english convention (mm/dd/yyyy). Using serial numbers usually helps.
Numbers are not a problem usually.

--
Regards,
Tom Ogilvy

Yep. i fixed typo. There must be something wrong with the Cell Format and

date column, because if i try to autofilter other column that contains
numbers only, code works.

 

[Cecilkumara Fernando]

PPesola,
Try,
Range("A1").AutoFilter Field:=4, Criteria1:=">=" &
cdbl(DateSerial(2004,5,27)), _
Operator:=xlAnd, Criteria2:="<=" & cdbl(DateSerial(2004,6,3))
Cecil

When i run my autofilter code, range with autofilter is empty ( except

autofilter columns ). Then if i select "(Custom...)" in autofilter list i
can see correct dates in criteria fields and after i cick OK, Autofilter
filter and show correct rows.

my autofilter code;

Range("a1").AutoFilter Field:=4, Criteria1:=">=27.5.2004" ,

Operator:=xlAnd, Criteria2:="<=3.6.2004"

 

[Guest]

Thanks Cecil, it works!!!

PPesola,
Try,
Range("A1").AutoFilter Field:=4, Criteria1:=">=" &
cdbl(DateSerial(2004,5,27)), _
Operator:=xlAnd, Criteria2:="<=" & cdbl(DateSerial(2004,6,3))
Cecil


autofilter columns ). Then if i select "(Custom...)" in autofilter list i
can see correct dates in criteria fields and after i cick OK, Autofilter
filter and show correct rows.
Operator:=xlAnd, Criteria2:="<=3.6.2004"