Autofilter for Todays date using VBA

dixon1983

Board Regular
Joined
Apr 23, 2005
Messages
175
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
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Maybe try:

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

AutoFilter can be tricky with dates, so post back if that doesn't work, specifying the column's number format.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
It worked when I tried it.

What does this tell you?

MsgBox Selection.Cells(2, 11).NumberFormat

Are your dates integers or do they include a time?
 

dixon1983

Board Regular
Joined
Apr 23, 2005
Messages
175

ADVERTISEMENT

It shows dd/mm/yyyy;@

Sub Hold()
Sheets("WsData").Select
Range("A1:K246").Select
Selection.AutoFilter
Selection.AutoFilter Field:=11, Criteria1:=Format(Date, Selection.Cells(2, 11).NumberFormat)
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("Current Day on Hold").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

End Sub

Thats the code im using
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
It still worked when I tried it with that number format.

Are your dates integers or do they include a time? What code do you get if you record a macro while AutoFiltering for today's date?
 

dixon1983

Board Regular
Joined
Apr 23, 2005
Messages
175

ADVERTISEMENT

They are integers.

I got the same code as above but with "19/10/2006" in the criteria1 area when I recorded a macro
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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
 

dixon1983

Board Regular
Joined
Apr 23, 2005
Messages
175
Neither of them have worked :(

Thanks for your the time you're spending on this by the way, i appreciate it.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
This is extremely frustrating (not your fault).

Does your recorded macro work when you run it?
 

Watch MrExcel Video

Forum statistics

Threads
1,112,937
Messages
5,543,093
Members
410,583
Latest member
gazz57
Top