Solved

How can I AutoFilter using two criteria in a macro?

Posted on 2012-04-02
4
260 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 41

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 41

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

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