Solved

Adding an additional filter to an Autofilter

Posted on 2011-09-15
6
266 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 32

Expert Comment

by:Rob Henson
ID: 36543660
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
ID: 36544000
Need everything <= to yesterday.  Thanks. -R-
0
 

Author Comment

by:RWayneH
ID: 36544220
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
ScreenConnect 6.0 Free Trial

Discover new time-saving features in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

 

Accepted Solution

by:
RWayneH earned 0 total points
ID: 36544938
Figured it out.

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

Thanks for the help!!  -R-
0
 

Author Closing Comment

by:RWayneH
ID: 36565476
Thanks for the help....  -R-
0
 

Author Comment

by:RWayneH
ID: 36544953
Figured it out on my own. -R-
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
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.

777 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