Forum Discussion
VBA
Hi im trying to make a macro to filter a date column (k1) by a date in a specific cell (a2 in sheet2).
I need two vba codes
- to filter the list to only include dates before the date given in that cell
- to filter that it should show dates equal or after the date in that cell
Thank you if you can help!
5 Replies
Hi,
Please try this macro and find it in the attached workbook:
Sub FilterDateBasedOnCellValue()
Dim criteria As Date
criteria = Sheet1.Range("A2").Value
Sheet2.Range("$K$1:$K$42").AutoFilter Field:=1, Criteria1:="<=" & criteria
End Subhi thanks for your help
I ran the macro from your workbook and get no results.
when i open the custom filter box it correctly displays the date and when i press ok it filters perfectly!!??
Before I posted my original message i recorded a macro to filter dates and had the same problem!
please help thank you!
Hi,
The code works for me!
But it seems a bug related to the regional date format in your PC.
I notice that you use the UK date format (dd/MM/yyyy).
I am currently using the US format (MM/dd/yyyy).
When I changed the regional date format in my PC to the UK date format, I got the same problem!
However, the workaround is to change the first line in the macro from this:
Dim criteria As Date
To this:
Dim criteria As Long
Hope that helps
