Adding an additional filter to an Autofilter

I am using the following code to extract data and need to add another filter.  It is to filter anything with a date of yesterday. (today -1)  Need to get all lines that are < today minus one.

See code below line 24.  How would I add this additional filter to column "T"?  -R-
Sub MDL_GetCompletedOrdersTodayMinusOneDay()
'On Format tab only
'Working on sending this total to the Dashboard too.

Dim rgFilter As Range, rgTarg As Range
Set rgTarg = Worksheets("MDL - Summary").Range("D8")  'Record count goes here
Sheets("Format").Select

With Worksheets("Format")       'Worksheet to be filtered
    Set rgFilter = .Range("A1")     'First header label in range to be filtered
    Set rgFilter = Intersect(.UsedRange, rgFilter.Resize(1, 100).EntireColumn)   'All the rows of data
    
    rgTarg.Formula = "=SUBTOTAL(3,'" & .Name & "'!" & rgFilter.Columns(3).Address & ")-1"
    With rgFilter
        .AutoFilter
        .AutoFilter Field:=7, Criteria1:="SPCLMDL"
        
        .AutoFilter Field:=10, Criteria1:="=CNF  LKD  REL", Operator:=xlOr, Criteria2:="=CNF  REL"
        .AutoFilter Field:=22, Operator:=xlFilterValues, Criteria2:=Array(2, Format(Date - 1, "m/d/yyyy")) 'Today minus one
'        .AutoFilter Field:=22, Operator:=xlFilterValues, Criteria2:=Array(2, Format(Date, "m/d/yyyy")) 'just today
                
        .Sort Key1:=Range("T1"), Order1:=xlAscending, Header:=xlGuess, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod:=xlPinYin
                
'Need to add a filter of only rows that are < today minus one

        rgTarg.Formula = rgTarg.Value
        Worksheets("Dashboard").Range("D9").Value = rgTarg.Value 'Send value to Dashboard
'        .AutoFilter     'Turn filters off
    End With
End With

Sheets("MDL - Summary").Select
Sheets("Format").Select
'    Range("C4").Select
    
End Sub

Open in new window

RWayneHAsked:
Who is Participating?
 
RWayneHConnect With a Mentor Author Commented:
Figured it out.

.AutoFilter Field:=20, Criteria1:="<" & Date, Operator:=xlAnd

Thanks for the help!!  -R-
0
 
Rob HensonFinance AnalystCommented:
Have you tried with the same syntax as line 16, ie:

.AutoFilter Field:=22, Criteria1:=Array(2, Format(Date - 1, "m/d/yyyy")) 'Today minus one

Do you want today and yesterday?

.AutoFilter Field:=22, Criteria1:=Array(2, Format(Date - 1, "m/d/yyyy")), Operator:=xlOr, Criteria2:=Array(2, Format(Date - 1, "m/d/yyyy"))

Thanks
Rob H
0
 
RWayneHAuthor Commented:
Need everything <= to yesterday.  Thanks. -R-
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
RWayneHAuthor Commented:
I just tried those two lines on Field:=20 and both lines return today minus one.  I need it to say <= to today, which will give me everything older than today. -R-
0
 
RWayneHAuthor Commented:
Thanks for the help....  -R-
0
 
RWayneHAuthor Commented:
Figured it out on my own. -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.