AutoFilter

[vpager]

Hi

In an atrtempt to set an AutoFilter on a column of dates...

Can anyone tell me why this code works...

Selection.AutoFilter Field:=7, Criteria1:=">=06/05/2011"


But this code doesn't...

istring = ">=06/05/2011"
Selection.AutoFilter Field:=7, Criteria1:=istring


The second code hides everthing whether iString is defined as a variant or String, or not defined at all.
Can't find any reference to this anywhere.


Thanks
Mike

[mancubus]

try:
http://www.vbaexpress.com/forum/archive/index.php/t-21655.html
http://www.ozgrid.com/VBA/autofilter-date-times.htm

[Chabu]

This works for me
Dim filterdate As Date
filterdate = DateSerial(2011, 9, 2)
Selection.AutoFilter Field:=3, Criteria1:=">=" & filterdate

[vpager]

Hi Guys, thanks for replies.
Unfortunately, I've tried all sorts of combinations and can't get a result.
The one that would suit my requirement best is:

Dim dDate as date, Wstarting as date

dDate = DateSerial(Year(WStarting), Month(WStarting), Day(WStarting))
Selection.AutoFilter Field:=7, Criteria1:=">=" & dDate



where Wstarting is set to the required date.
But the Autofilter still hides all the rows despite column 7 containing many qualifying entries.

I may have to rethink the way I'm approaching this. I prefer to use code that does what it should rather than having to use work-arounds. Even if that means re-writing.

Thanks
Mike

[frank_m]

HI Mike,

This is what I use, but may need modification depending on your regional settings:
Dim strDate As String

strDate = "06/05/2011"

strDate = Format(strDate, "mm/dd/yyyy")

Selection.AutoFilter Field:=7, Criteria1:=">=" & strDate, _
Operator:=xlAnd, Criteria2:="<=" & strDate

[jreedich]

Try changing the line
istring = ">=06/05/2011"
to
istring = """" & ">=06/05/2011" & """"

[Chabu]

IT should work, the exact same code works for me.
Can you post your spreadsheet?

[vpager]

Thanks for all your replies and suggestions. For the time being, I've re-worked my code to use the date strings in their oringinal text format of yyyymmdd.
But I will play around with your suggestions shortly as I do like to know answers to such issues as I come across them

Regards