View Full Version : AutoFilter
vpager
05-27-2011, 09:20 AM
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
05-27-2011, 10:09 AM
try:
http://www.vbaexpress.com/forum/archive/index.php/t-21655.html
http://www.ozgrid.com/VBA/autofilter-date-times.htm
Chabu
05-27-2011, 12:48 PM
This works for me
Dim filterdate As Date
filterdate = DateSerial(2011, 9, 2)
Selection.AutoFilter Field:=3, Criteria1:=">=" & filterdate
vpager
05-31-2011, 12:16 AM
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
05-31-2011, 10:32 AM
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
05-31-2011, 10:39 AM
Try changing the line
istring = ">=06/05/2011"
to
istring = """" & ">=06/05/2011" & """"
Chabu
05-31-2011, 11:22 AM
IT should work, the exact same code works for me.
Can you post your spreadsheet?
vpager
06-01-2011, 12:24 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.