Autofilter for Todays date using VBA

[dixon1983]

Hi,
I have a macro that I want to autofilter a sheet of data with the criteria being Todays date.

What criteria should I put in the line of code below and what format should the data in the column be in?

Selection.AutoFilter Field:=11, Criteria1:= ???

I have tried putting TODAY and NOW in there but it doesnt work.

Thanks for any help

 

[dixon1983]

Yes the recorded macro does work with "18/10/2006" in the criteria

 

[dixon1983]

Is there a way you could get it to return the Max value in the column for the filter? This would do also.

 

[Andrew Poulsom]

If your recorded macro works I don't understand why:

Selection.AutoFilter Field:=11, Criteria1:=Format(Date, Selection.Cells(2, 11).NumberFormat)

doesn't work.

How about?

Selection.AutoFilter Field:=11, Criteria1:=Format(Date, "dd/mm/yyyy")

Selection.AutoFilter Field:=11, Criteria1:=Format(Date, "Short Date")

Selection.AutoFilter Field:=11, Criteria1:=Format(WorksheetFunction.Max(Selection.Columns(11)), "dd/mm/yyyy")

 

[dixon1983]

Hi andrew,
I'm just leaving work now. I will try the suggested codes tomorrow when I get in and let you know if any of them work.

Thanks a lot for all of your help on this matter.

 

[dixon1983]

Andrew,
Just got the bottom one of the 3 working. Works a treat now.

Thanks a lot for all your help, much appreciated.

 

[annoorr]

Try one of these:

Selection.AutoFilter Field:=11, Criteria1:=CLng(Date)

Selection.AutoFilter Field:=11, Criteria1:=">" & CLng(Date) - 1, Operator:=xlAnd, Criteria2:="<" & CLng(Date) + 1

The second one is perfect.. Thankssssssssssssssssssssssssssssssssssssssssssssss Mr. Andrew Poulsom

Thank you a lot.. you've save me a lot of tiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiime

 

[sivavuddanti]

Hi , I need some help for above code , I am trying to get last 5 business days from today by filtering but I am unable to getting that , I tried by changing code in different ways