VBA Filtering by date

[Obbsie]

Hi All

I am trying write code to filter for all data before today's date which is contained in a cell. I assumed that it would be pretty basic and that filtering using the "<" symbol would work. It works for standard numbers, but dates don't seem to like it. The filter determines that there are no results prior to the requested date. Just as a test, I also tried using the ">" symbol for dates after today and it returned all the results that had only text in the date field. What am I missing?

Here's the code I am using (2003 Excel).

Sub FilterExpiryDate()

    Selection.AutoFilter Field:=9, Criteria1:="<" & Range("A1").Value, Operator:=xlAnd
    Range("C7").Select
    
End Sub

 

[Obbsie]

It seems to work when I change the formatting of the cell containing today's date to a number instead. It recognises it as todays date and filters accordingly. I can keep it like this, but I would prefer to have that cell displayed as a date format too.

 

[Tom Urtis]

Try either this one-liner...

Range("I1:I" & cells(rows.count, 9).end(xlup).row).AutoFilter field:=1, Criteria1:="<" & CDbl(DateSerial(year(date), month(date). day(date))

...or this macro that covers the situation from the get-go:

Sub Test1()
ActiveSheet.AutoFilterMode = False
Dim FilterRange As Range, myDate As Date
myDate = DateSerial(Year(Date), Month(Date), Day(Date))
Set FilterRange = Range("I1:I" & Cells(Rows.Count, 9).End(xlUp).Row)
FilterRange.AutoFilter Field:=1, Criteria1:="<" & CDbl(myDate)
End Sub

 

[Obbsie]

Thanks for your help Tom.

I've tried both options you suggested, but because of the layout of the sheet, they don't quite work the way I'd like them to. I'll just stick with having the reference date in a number format and the original code. It does the job and the end user isn't going to know the difference when they push the button. I was just being pedantic