Filter for Current Date

[jmdaniel]

Tom,

You helped me out earlier when I was filtering by Saturday of the current
week:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Well sure, but why would you hard code the date when you can use the Date
function to get today's date. That is why you use macros, - to make things
dynamic - or at least one reason.

On my test sheet, I had my dates in column 2 with headers in Row1 and
staring in A1:

Sub Macro1()
ActiveSheet.AutoFilterMode = False
Set rng = ActiveSheet.Range("A1").CurrentRegion
s = rng(1).Offset(1, 1).NumberFormat
sStart = Format(Date, s)
sSaturday = Format(Date + 7 - Weekday(Date, 1), s)
rng.AutoFilter Field:=2, Criteria1:=">=" & sStart, _
Operator:=xlAnd, _
Criteria2:="<=" & sSaturday

End Sub

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

How would you modify this to filter a column by the current date only? Thanks.

 

[Tom Ogilvy]

Sub Macro1()
ActiveSheet.AutoFilterMode = False
Set rng = ActiveSheet.Range("A1").CurrentRegion
s = rng(1).Offset(1, 1).NumberFormat
sStart = Format(Date, s)
sEnd = Format(Date + 1, s)
rng.AutoFilter Field:=2, Criteria1:=">=" & sStart, _
Operator:=xlAnd, _
Criteria2:="<" & sEnd

End Sub

 

[jmdaniel]

Thanks!

Sub Macro1()
ActiveSheet.AutoFilterMode = False
Set rng = ActiveSheet.Range("A1").CurrentRegion
s = rng(1).Offset(1, 1).NumberFormat
sStart = Format(Date, s)
sEnd = Format(Date + 1, s)
rng.AutoFilter Field:=2, Criteria1:=">=" & sStart, _
Operator:=xlAnd, _
Criteria2:="<" & sEnd

End Sub