Filter on today's date

[ced0802]

Hi everyone,

I have a file with date on format 12/01/2016. I would like to filter on today's date. Dates are on column 29.

Thank you very much in advance for your help.

[mancubus]

with european date format, try:



With Worksheets("MySheet") 'change MySheet to suit
.AutoFilterMode = False
.Cells(1).AutoFilter Field:=29, Criteria1:="=" & CLng(DateSerial(Year(Date), Month(Date), Day(Date)))
End With

[ced0802]

Thank you very much Macubus, really appreciate it.

Unfortunately it is not working. It is running but end up with nothing on filter.

[Paul_Hossler]

This assumes that the date column is formatted as dates (*m/dd/yyyy) so that regional changes are picked up

I tried US format (1/12/2016) and then changed the PC Region to UK to test UK format (12/1/2016)




Option Explicit
Sub Macro1()
With ActiveSheet
.AutoFilterMode = False
.Cells(1, 1).AutoFilter Field:=29, Criteria1:="=" & CStr(DateSerial(Year(Date), Month(Date), Day(Date)))
End With
End Sub

[mancubus]

you are welcome.


?


With Worksheets("MySheet") 'change MySheet to suit
.AutoFilterMode = False
.Cells(1).AutoFilter Field:=29, Criteria1:="=" & Format(Date, "dd/mm/yyyy")
End With

[ced0802]

Working PERFECTLY !!

Thanks a lot for that !!

[mancubus]

you are welcome. thanks for the feed back and marking the thread as solved.


.Cells(1).AutoFilter Field:=29, Criteria1:="=" & Date
worked as well with my test file

[Paul_Hossler]

Your #7 is elegant and looks like it 'travels' well (different PC regions)

I think that #5 would not work with US formats, so #7 is more robust

[mancubus]

@Paul
regarding post #2, yes it is for european date format. it is sometimes really a headache to work with dates in VBA with non-US date format settings.

strange thing is, the bit in post 2 is from a working procedure that i currently use and i am surprised to see it did not work for my test file either. :)

[SamT]

Filters are one of those things that only work with US formats :banghead:

@All, Please note that Mancubus' solutions are specific to the current Date. It is the same as

.Cells(1).AutoFilter Field:=29, Criteria1:="=" & Format(Now, "dd/mm/yyyy")