filter with multiple criteria

[rob1987]

Hello, can somebody please explain where my Macro is going wrong. I want to filter my data and return any rows in which the content of column c matches the content of either cell b1, c1 or d1.

when I use...:

Sub Filter_Stuff()

With Sheets("example 2")


Range("c4:g200").AutoFilter Field:=1, Criteria1:=Range("b1"), Operator:=xlOr, Criteria2:=Range("c1")

End With
End Sub

...It works fine to filter based on the reference in b1 and c1 but when I add d1 it doesn't recognise the third criteria:


Sub Filter_Stuff()

With Sheets("example 2")


Range("c4:g200").AutoFilter Field:=1, Criteria1:=Range("b1"), Operator:=xlOr, Criteria2:=Range("c1"), Operator:=xlOr, Criteria3:=Range("d1")
End With
End Sub

Can anybody help please?

Many Thanks

 

[Gerald Higgins]

I may be wrong but I think that's a basic limitation in Excel, you can only filter on two options in the same field.

There maybe ways round this, depending on how your data is set up . . .

 

[Andrew Poulsom]

You can only have 2 AutoFilter criteria per column. For more you need Advanced Filter:

http://www.contextures.com/xladvfilter01_2003.html

 

[rob1987]

Thanks, I'll have a look at Advanced Filters