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

 

[tharless]

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?

 

[Kerrold]

Thank you Farless, greatly appreciated

 

[Kerrold]

Thank you Farless, greatly appreciated

Sorry that should have sent Thank you Tharless, greatly appreciated

 

[tharless]

I knew what you meant, and you are welcome, did it work out for you correctly?

 

[Marcelo Branco]

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?

Say you have a vertucal (column) list named MyList.

Maybe this

Sub aTest()
    Dim v As Variant
    
    v = Application.Transpose(Range("MyList"))
    Range("A1").Select
    Selection.AutoFilter Field:=1, Criteria1:=v, Operator:=xlFilterValues
End Sub

M.

 

[Kerrold]

Unfortunately it didn't work but I found somewhere that multiple criteria doesn't work on excel 2003 only on the more recent version though not sure which ones. Sorted it with a workaround just a bit long winded in comparison. Thank you

 

[Marcelo Branco]

It worked for me (Excel 2010)

M.

 

[Wenlei]

I guess the code doesn't work because of the extra _, try this.
Selection.AutoFilter Field:=10, Criteria1:="=SERCO", Operator:=xlOr, _
Criteria2:="=NG", Operator:=xlOr, Criteria3:="=NSC"

 

[tharless]

Unfortunately it didn't work but I found somewhere that multiple criteria doesn't work on excel 2003 only on the more recent version though not sure which ones. Sorted it with a workaround just a bit long winded in comparison. Thank you

It should work Kerrold, I am using excel 2007, but the worksheet I have the code in is in 2003 format, it was origanally created in 2003. Make sure anywhere you have _ you have a space before and after, and you could take that out if you wanted and make it just one long line instead, the _ is simply to allow you to continue the code on the next line.