[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 299
  • Last Modified:

How can I AutoFilter using two criteria in a macro?

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
user2073
Asked:
user2073
  • 2
  • 2
1 Solution
 
dlmilleCommented:
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
 
user2073Author Commented:
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
 
dlmilleCommented:
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
 
user2073Author Commented:
You guys ar fabulous Thanks very much. The extra code works a treat.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now