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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

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?
 

Forum statistics

Threads
1,141,429
Messages
5,706,405
Members
421,447
Latest member
arthuro2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top