Autofilter vba dont show filtered data

P

PPesola

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
 
T

Tom Ogilvy

Try

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

--
Regards,
Tom Ogilvy

PPesola said:
When i run my autofilter code, range with autofilter is empty ( except
Click to expand...
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" ,
Click to expand...
Operator:=xlAnd, Criteria2:="<=3.6.2004"
 
P

PPesola

Thanks Tom, but it didnt work :(


Tom Ogilvy said:
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"
Click to expand...
 
T

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

PPesola

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

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

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

Cecilkumara Fernando

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

PPesola said:
When i run my autofilter code, range with autofilter is empty ( except
Click to expand...
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" ,
Click to expand...
Operator:=xlAnd, Criteria2:="<=3.6.2004"
 
P

PPesola

Thanks Cecil, it works!!!

Cecilkumara Fernando said:
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"
Click to expand...
 
Top