Solved

How can I AutoFilter using two criteria in a macro?

Posted on 2012-04-02
4
267 Views
Last Modified: 2012-08-13
I'm trying to AutoFilter with two criteria the second is "Not Equal To" in Text.
The code works for one filtering but fails on the second can some assist me by changing the code attached.

Thanks
OverdueTest2.xlsm
0
Comment
Question by:user2073
  • 2
  • 2
4 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 37798987
Your autofilter needs to extend all the way to column F.  Here it is corrected:

    ActiveSheet.Range("$A$1:$F$50").AutoFilter Field:=4, Criteria1:="<=" & CStr(Date2), Operator:=xlFilterValues
    
    ActiveSheet.Range("$A$1:$F$50").AutoFilter Field:=6, Criteria1:="<>Destroyed", Operator:=xlFilterValues
    

Open in new window


See attached with this correction.

Cheers,

Dave
OverdueTest2-r1.xlsm
0
 

Author Comment

by:user2073
ID: 37799121
Thanks for the solution it works great, if I use your file or copy your file to another computer. But if I change my code to be the same as your worksheet or copy your code into my worksheet, the macro stops on the second line of criteria every time any suggestions.
0
 
LVL 42

Accepted Solution

by:
dlmille earned 500 total points
ID: 37799140
You may need to turn auto filter off first.

Try this (See line 10):

Sub FiltersColD()
'This macro filters Col."D" for dates before Todays Date and Not have "Destroyed" in column "F".
'Then copies to worksheet "Overdue".

Dim Date2 As Date
Date2 = Now()

Sheets("Data1").Select

    If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
    
    ActiveSheet.Range("$A$1:$F$50").AutoFilter Field:=4, Criteria1:="<=" & CStr(Date2), Operator:=xlFilterValues
    
    ActiveSheet.Range("$A$1:$F$50").AutoFilter Field:=6, Criteria1:="<>Destroyed", Operator:=xlFilterValues
    
    Range("A2:A300, D2:D300").Select
    Selection.Copy
    Sheets("Overdue").Select
    Range("A3").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

End Sub

Open in new window


Dave
0
 

Author Closing Comment

by:user2073
ID: 37799164
You guys ar fabulous Thanks very much. The extra code works a treat.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

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…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

831 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