PDA

View Full Version : [SOLVED] Filter on today's date



ced0802
01-12-2016, 05:59 AM
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
01-12-2016, 07:07 AM
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
01-12-2016, 07:26 AM
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
01-12-2016, 07:43 AM
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
01-12-2016, 07:54 AM
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
01-12-2016, 08:17 AM
Working PERFECTLY !!

Thanks a lot for that !!

mancubus
01-12-2016, 09:00 AM
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
01-12-2016, 10:12 AM
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
01-12-2016, 12:30 PM
@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
01-12-2016, 01:09 PM
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")