help on Custom filters

[compusheaf]

I am trying to do a custom filter on column A where i have dates in it. I want to do a custom filter as Greater than xyz AND Less Than or equal to abc. The same works when I do it manually in excel, but when i record a code in VBA it does not work. Any reasons ?

 

[pgc01]

Hi compusheaf

You forgot to post the code, so it's not possible to know what's wrong.

This one works for me:

Columns("A").AutoFilter Field:=1, Criteria1:=">=2007-07-05", Operator:=xlAnd, Criteria2:="<=2007-07-10"

You may have to adapt the date formats to your regional settings.

Kind regards
PGC

 

[compusheaf]

Here is the code

Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=">=" & stdate, Operator:=xlAnd _
, Criteria2:="<=" & enddate

stdate and enddate are two string variables storing dates

If I try it doing manually it works but not with this code

 

[pgc01]

What are stdate and enddate? How are they declared and initialized?

I just tried 2 tests, the first one with variables and the second one reading from the worksheet and had no problem:

Sub Test1()
Dim stdate As Date, enddate As Date

stdate = CDate("2007-07-05")
enddate = CDate("2007-07-10")

Columns("A").AutoFilter Field:=1, Criteria1:=">=" & stdate, Operator:=xlAnd, Criteria2:="<=" & enddate
End Sub

Sub Test2()

Columns("A").AutoFilter Field:=1, Criteria1:=">=" & CDate(Range("d2")), Operator:=xlAnd, _
                                      Criteria2:="<=" & CDate(Range("e2"))
End Sub

Hope this helps
PGC