Solved

Adding an additional filter to an Autofilter

Posted on 2011-09-15
6
261 Views
Last Modified: 2012-06-27
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

0
Comment
Question by:RWayneH
  • 5
6 Comments
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
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
 

Author Comment

by:RWayneH
Comment Utility
Need everything <= to yesterday.  Thanks. -R-
0
 

Author Comment

by:RWayneH
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Accepted Solution

by:
RWayneH earned 0 total points
Comment Utility
Figured it out.

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

Thanks for the help!!  -R-
0
 

Author Closing Comment

by:RWayneH
Comment Utility
Thanks for the help....  -R-
0
 

Author Comment

by:RWayneH
Comment Utility
Figured it out on my own. -R-
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now