Filter based on Date

  • Thread starter Thread starter Abdul
  • Start date Start date
A

Abdul

Hi,

Always I face a problem when i try to filter a range based on date. If
I do it manually it works fine. But when I try to do this thru code it
dont work.

May be there is a better way to do this?

when I recoded my action i get:

Sheets("Detail").select
Range("A1").AutoFilter Field:=1, Criteria1:=">=01/07/2006",
Operator:=xlAnd _
, Criteria2:="<=31/07/2006"

and when i run this code again no records are shown !!!
 
Damn US dates <g>

Dim date1_value As Date
Dim date2_value As Date

Sheets("Detail").Select
date1_value = DateValue("01-Jul-2006")
date2_value = DateValue("31-Jul-2006")
Range("A1").AutoFilter Field:=1, _
Criteria1:=">=" & Str(CDbl(date1_value)), _
Operator:=xlAnd, _
Criteria2:="<=" & Str(CDbl(date2_value))


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Just wondering, is it necessary to do all that or simply

Range("A1").AutoFilter Field:=1, _
Criteria1:=">=07/Jul/2006", _
Operator:=xlAnd _
, Criteria2:="<=31/Aug/2006"

as long as the month is spelt and in US date order

Regards,
Peter T
 
Hi Peter,

My suggestion was more of a generic solution. It should work wherever you
get the date from, a cell, a userform etc., and filter correctly. You have
effectively shortcut my solution for those particular dates.

Regards

Bob
 
Hi Bob,

I have always found that providing a date string is un-ambiguous it
correctly converts. Not sure about the "-" as date separator in all systems
(but "/" is always OK), also not sure if "July" is recognized in all
languages. But I see your solution would be better practice, I'll try not
to be lazy <g>

Regards,
Peter T
 
Peter,

I absolutely agree about unambiguous dates. When I use is SUMPRODUCT
solutions I try to use an ISO standard yyyy-mm-dd, I haven't yet heard from
anyone that "-" is a problem. And certainly July would not be recognised in
Excel with our friends across the channel.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)
 
And certainly July would not be recognised in
Excel with our friends across the channel.
Click to expand...

'Le Weekend' seems to be understood well enough, perhaps it's just a matter
of time...

Regards,
Peter T
 
Maybe you should suggest that to President Chirac ... I'll stand back.

Regards

Bob
 
A while back when he was a mayor I did quip something along those lines to
him. But even given in the best langue diplomatique I could muster I got the
impression it wasn't entirely appreciated.

Regards,
Peter T
 
Back
Top