VBA - AutoFilter with more than 2 criteria

[kgkev]

I would like to autofilter with 5+ criteria, but my code falls over if I try more than 2.

is there a work around or alternative method without stepping through each line?

Works

Sheets("data").Range("A:P").AutoFilter Field:=1, Criteria1:="<>R2*" _
        , Operator:=xlAnd, Criteria2:="<>T2*"

Doesn't Work

Sheets("data").Range("A:P").AutoFilter Field:=1, Criteria1:="<>R2*" _
        , Operator:=xlAnd, Criteria2:="<>T2*" _
        , Operator:=xlAnd, Criteria3:="<>N2*" _
        , Operator:=xlAnd, Criteria4:="<>B2*" _
        , Operator:=xlAnd, Criteria5:="<>DEV*"

 

[Fluff]

You can only use more than 2 criteria if you are filtering on exact matches.
One way is to you advance filters instead.
https://www.contextures.com/exceladvancedfiltervba.html

 

[kgkev]

Thanks for that, looks like advancedfilter will replace a significant part of my code, but I can't quite see where the actual criteria is specified, or how to add multiple criteria

     Sheets("data").Range("A:P").AdvancedFilter _        Action:=xlFilterCopy, _
        CriteriaRange:=Sheets("data").Range("A:A") := "<>R2*", _
        CriteriaRange:=Sheets("data").Range("A:A") := "<>T2*", _
        CriteriaRange:=Sheets("data").Range("A:A") := "<>B2*", _
        CriteriaRange:=Sheets("data").Range("A:A") := "<>DEV*", _
        copytorange:=Sheets("CTB").Range("A1") ', _ ??
       ' Unique:=True ??

 

[Fluff]

Have a look here
https://www.contextures.com/xladvfilter01.html

 

[Excel_Phoenix]

A blast from the past ...

https://www.mrexcel.com/forum/excel-questions/52874-autofilter-more-than-2-criteria.html

 

[kgkev]

Fluff, I am sorry but I cannot get my head around why this doesn't work.

I have simplified the criteria just for testing and the advance filter doesn't give any error, however it returns all data.

Sheets("data").Range("A:P").AdvancedFilter _
        Action:=xlFilterinplace, _
        CriteriaRange:= Sheets("Filter_Data").Range("A1")

The value in Sheets("Filter_Data").Range("A1") is T2*

 

[kgkev]

Worked it out. I needed to have a header in my CriteriaRange

 

[Fluff]

Glad you sorted it & thanks for the feedback