[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

How can I AutoFilter using two criteria in a macro?

Posted on 2012-04-02
4
Medium Priority
?
295 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

650 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