• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 276
  • Last Modified:

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

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 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
'    Range("C4").Select
End Sub

Open in new window

  • 5
1 Solution
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"))

Rob H
RWayneHAuthor Commented:
Need everything <= to yesterday.  Thanks. -R-
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-
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

RWayneHAuthor Commented:
Figured it out.

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

Thanks for the help!!  -R-
RWayneHAuthor Commented:
Thanks for the help....  -R-
RWayneHAuthor Commented:
Figured it out on my own. -R-
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.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now