PDA

View Full Version : Solved: Dates in Filter macro



peacenik
10-15-2008, 02:44 PM
I have a macro that I use often to filter data based on the current cell contents.

Sub filterthis()
FilterValue = ActiveCell.Value
On Error Resume Next
'ActiveSheet.ShowAllData
Selection.AutoFilter Field:=ActiveCell.Column, Criteria1:="=" & FilterValue, Operator:=xlAnd
End Sub

It works really well, except when the current cell is a date field. Can anyone suggest an improvement that would make it work for dates as well.

Thanks.

Bob Phillips
10-15-2008, 02:52 PM
Dates are problemmatical in autofilter, but can be overcome. Is this a generic routine that needs to handle many data types, or do you just want a rouine to filter dates?

peacenik
10-15-2008, 02:56 PM
I use it all the time, with all types of data. (When combined with an unfilter macro, it makes it really easy to slice and dice data using shortcut keys).

However, if you could give me some hints, I could test for a date and then branch to whatever conversion is required.

Can you give me some guidance.

Bob Phillips
10-15-2008, 03:47 PM
As I say, dates are odd, and I usually try one of two things, compare twice for >= AND <= the date being tested for (sic!), or just test for a string formatted as the same format as the first dat in the range being filtered.

peacenik
10-15-2008, 06:11 PM
Thanks for the hints. I have worked it out now.

Here is what I have done.

Sub filterthis()
formattest = ActiveCell.NumberFormat
Select Case formattest
Case "mmm-yy", "dd/mm/yyyy"
FilterValue = Format(ActiveCell.Value, formattest)

Case Else
FilterValue = ActiveCell.Value
End Select


On Error Resume Next
Selection.AutoFilter Field:=ActiveCell.Column, Criteria1:="=" & FilterValue, Operator:=xlAnd
End Sub


All I have to do now is to add additional formats to the Case Statement.
:joy: