Autofilter equal to or less than today?

How do I write an autofilter that says =< today?

   ActiveSheet.RangeUsed.AutoFilter Field:=37, Operator:= _
    xlFilterValues, Criteria2:=Array(0, DateValue(Now) - 1)

Open in new window

RWayneHAsked:
Who is Participating?
 
Jerry PaladinoConnect With a Mentor Commented:
I believe it was failing because you had "RangeUsed" instead of "UsedRange".  
     ActiveSheet.UsedRange.AutoFilter Field:=37, _
    Criteria1:="<=" & Format(Date, "m/d/yyyy"), Operator:=xlFilterValues

Open in new window

Edited - Sorry - I copied the wrong one
0
 
FlysterCommented:
You can try:
   ActiveSheet.RangeUsed.AutoFilter Field:=37, Operator:= _
    xlFilterValues, Criteria2:="<=" & Array(0, DateValue(Now) ) 

Open in new window

Flyster
0
 
RWayneHAuthor Commented:
For some reason this is failing.  I forgot too that I need to take out the blanks and the #N/A's.  All I need to see are the cells that have dates and are =< today.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Jerry PaladinoCommented:
ActiveSheet.RangeUsed.AutoFilter Field:=37, Operator:= _
    xlFilterValues, Criteria2:="<=" & Format(Date, "m/d/yyyy")

This should eliminate blanks and #NA
0
 
RWayneHAuthor Commented:
Here is the sheet that I am trying to run the autofilter on and here is the code that is failing.

' Macro13 Macro
'Sheets("CDPSRECRPT-Yesterday").Select
    Rows("1:1").Select
    Selection.AutoFilter  'filter on
   
'    ActiveSheet.RangeUsed.AutoFilter Field:=37, Operator:= _
'    xlFilterValues, Criteria2:="<=" & Array(0, DateValue(Now))
    
    ActiveSheet.RangeUsed.AutoFilter Field:=37, Operator:= _
    xlFilterValues, Criteria2:="<=" & Format(Date, "m/d/yyyy")

    RowCnt2 = ActiveSheet.UsedRange.Columns(1).SpecialCells(xlVisible).Count
    Sheets("MOT-MeasureData").Select
    Range("C2").Select 'put the number complete in the previous day
    Range("C3") = RowCnt2  'places the number in MOT-MeasureData

'

End Sub

Open in new window

CDPS.xlsx
0
 
RWayneHAuthor Commented:
Thanks for the help. -R-
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.