Filter with multiple criteria in VBA

[Kerrold]

Hi

The following code is quite straight forward and looks for 2 criteria using a filter within some data but I need it to look for more than 2 criteria now as the report is growing...

Selection.AutoFilter Field:=10, Criteria1:="=SERCO", Operator:=xlOr, _
Criteria2:="=NG"

I have tried adding a 3rd criteria based on the code above as show below

Selection.AutoFilter Field:=10, Criteria1:="=SERCO", Operator:=xlOr, _
Criteria2:="=NG", Operator:=xlOr, _ Criteria3:='=NSC"

but it won't work. Any help appreciated

Thank you in advance
Kerry

 

[MeghaJ]

this code works gr8 with text. For me this code is not working if i have only numerical data. Could you please let me knwo if you can help?

 

[Peter_SSs]

this code works gr8 with text. For me this code is not working if i have only numerical data. Could you please let me knwo if you can help?

Answer suggested here

 

[Kerrold]

Hi

I am just writing some code and need to get it to select everything but 2 critera from a drop down list, i have started with ...

Selection.AutoFilter Field:=15, Criteria1:=Array("Glasgow","Glasgow Tier 1"), Operator:=xlFilterValues

But this selects the two criteria mentioned, i need it to not select these two, can i do that using this code??

Thank you
Kerry

 

[Marcelo Branco]

Hi Kerry,

Maybe you can use Advanced Filter with a formula

Something like this

Assumes
Data in Sheet1 columns A-O ( so Field15 is in column O)
Headers in row 1
Cells Z1 and Z2 are free (the code inserts a formula in Z2)

Adapt to your real data setup

Sub aTest()
    Dim myArr() As Variant, myStr As String
    
    With Sheets("Sheet1")
        If .FilterMode Then .ShowAllData
        myArr = Array("Glasgow", "Glasgow Tier 1")
        myStr = "{""" & Join(myArr, """,""") & """}"
        'Debug.Print myStr
    
        .Range("Z2").Formula = "=ISNA(MATCH($O2," & myStr & ",0))"
    
        .Range("A1").CurrentRegion _
            .AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
            .Range("Z1:Z2"), Unique:=False
    End With
End Sub

Hope this helps

M.

 

[ajay14]

Please try this template:

Dim index as integer starting position of list in excel
Dim EndValue as integer 'end position of of list i.e. index+number of items
index=5
EndValue =index+20



For i = indexTo EndValue 'each loop will have one index which will be looped
Sheets("Sheet 1").Select
Cust = Cells(i, 4).Value ' position of list ith row 4th column
ActiveSheet.UsedRange.AutoFilter Field:=7, Criteria1:="=*" & Cust & "*", Operator:=xlAnd
'Filter Requests

'Your code here
'Your code here
'Your code here
'Your code here


'unfilter
Cells.AutoFilter
Next

 

[Raghavendra Kamath]

For multiple column filter Below code works
set aa=Createobject("Excel.Application")
set b=aa.Workbooks.open("E:\AutoFilter.xlsx")
set c=aa.worksheets("sheet1")
c.cells(1,1).autofilter 1,"US"
c.cells(1,1).autofilter 2,"a"
set aa=Nothing
msgbox "Done"

 

[Raghavendra Kamath]

For multiple columns below code works fine
set aa=Createobject("Excel.Application")
set b=aa.Workbooks.open("E:\AutoFilter.xlsx")
set c=aa.worksheets("sheet1")
c.cells(1,1).autofilter 1,"US"
c.cells(1,1).autofilter 2,"a"
set aa=Nothing
msgbox "Done"

 

[Raghavendra Kamath]

Correctly formatted code
set Excel=Createobject("Excel.Application")
set Workbook=Excel.Workbooks.open("E:\2015_02_25_Changed_Data_For_Release_853.xlsx")
set Worksheet=Excel.worksheets("New Brands")
Worksheet.Cells(1,1).autofilter 1,"U.S. English"
set Excel=Nothing
Msgbox "Done"

 

[ExcelGuest]

Hello Kerrold,
I have a similar setup, however I use my code compiling multiple sheets into one and only copying data based on my filter so it may need some tweeking but should help.

Range("A1").Select
    Selection.AutoFilter Field:=10, Criteria1:=Array( _
        "   IL", "   IN", "   MI", "   OH", "   WV"), Operator:=xlFilterValues

I am obviuosly filtering on the 5 states listed, just replace with your criteria.

I also need some help if any knows how to tweek this, in the above AutoFilter instead of hard coded criteria I need to get the criteria from a list in column a of sheet1. Anyone know how to word that?

Here is a good example for selecting multiple criteria from separate columns where you would not want to see specific text/numbers:
With Range("J:M")
.AutoFilter
.AutoFilter Field:=1, Criteria1:="<>Apples", Operator:=xlAnd, _
Criteria2:="<>Oranges", Operator:=xlAnd
.AutoFilter Field:=4, Criteria1:="<>Organic"
End With

 

[ExcelGuest]

Here is a good example for selecting multiple criteria from separate columns where you would not want to see specific text/numbers:
With Range("J:M")
.AutoFilter
.AutoFilter Field:=1, Criteria1:="<>Apples", Operator:=xlAnd, _
Criteria2:="<>Oranges", Operator:=xlAnd
.AutoFilter Field:=4, Criteria1:="<>Organic"
End With

You can remove that first .AutoFilter as it would not be necessary.