VBA Filtering by date

Obbsie

New Member
Joined
Nov 20, 2011
Messages
15
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).

Code:
Sub FilterExpiryDate()

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

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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.
 
Upvote 0
Try either this one-liner...
Code:
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:

Code:
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
 
Upvote 0
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 :rolleyes:
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,108
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top