[RESOLVED] Autofilter Criteria limitation / error

[Starbucks Junkie]

Guys,

I am trying to filter on more than 2 sets of criteria but am unable to make the code work, is the code wrong or have I breached the limitation of the filter property?

Selection.AutoFilter Field:=16, Criteria1:="=CN", Operator:=xlOr, _
        Criteria2:="=HK", Operator:=xlOr, _
        criteria3:="=ID", Operator:=xlOr, _
        criteria4:="=IN", Operator:=xlOr, _
        criteria5:="=PK", Operator:=xlOr, _
        criteria6:="=TH", Operator:=xlOr, _
        criteria7:="=TW"

Let me know, many thanks

SJ

[Siddharth Rout]

Guys,

I am trying to filter on more than 2 sets of criteria but am unable to make the code work, is the code wrong or have I breached the limitation of the filter property?

Selection.AutoFilter Field:=16, Criteria1:="=CN", Operator:=xlOr, _
        Criteria2:="=HK", Operator:=xlOr, _
        criteria3:="=ID", Operator:=xlOr, _
        criteria4:="=IN", Operator:=xlOr, _
        criteria5:="=PK", Operator:=xlOr, _
        criteria6:="=TH", Operator:=xlOr, _
        criteria7:="=TW"

Let me know, many thanks

SJ

On one field you can only use 2 criterias. Have you checked the "Filter by using advanced criteria" option which is also known as "Advanced filter"? you can check it out under Data->Filter->Advanced Filter

The Advanced Filter command can filter a range in place like the AutoFilter command, but it does not display drop-down lists for the columns. Instead, you type the criteria you want to filter by in a separate criteria range above the range. A criteria range allows for more complex criteria to be filtered.

[Starbucks Junkie]

On one field you can only use 2 criterias. Have you checked the "Filter by using advanced criteria" option which is also known as "Advanced filter"? you can check it out under Data->Filter->Advanced Filter

The Advanced Filter command can filter a range in place like the AutoFilter command, but it does not display drop-down lists for the columns. Instead, you type the criteria you want to filter by in a separate criteria range above the range. A criteria range allows for more complex criteria to be filtered.

Kool,

Is there a VBA command to do this?

[Siddharth Rout]

Kool,

Is there a VBA command to do this?

yes there is but the first thing that you need to do is understand what Advanced Filter command does. The Excel help is good enough to expalin as it also have examples. once the thought process is clear then you need to record a macro and do what you want with Advanced Filter command. That will set you going in the right direction... If you still get stuck ... post

[Starbucks Junkie]

yes there is but the first thing that you need to do is understand what Advanced Filter command does. The Excel help is good enough to expalin as it also have examples. once the thought process is clear then you need to record a macro and do what you want with Advanced Filter command. That will set you going in the right direction... If you still get stuck ... post

ok,

I will give it a go.

Thanks

I will leave this thread open, in case I have any questions but will be sure to mark it resolved if I get it to work.

[Starbucks Junkie]

ok,

I will give it a go.

Thanks

I will leave this thread open, in case I have any questions but will be sure to mark it resolved if I get it to work.

Kool,

This is the code that I have written to perform the advanced filter, and the advanced filter works, but I also need to have additional filters on the data and the rest of the code will now not work...

Is it because I am trying to combine filters?

Is there any way I can make this work, without trying to advance filter on everything?

Range("p1:p60000").AdvancedFilter action:=xlFilterInPlace, criteriarange:= _
    Range("u1:u8"), unique:=False


    Selection.AutoFilter Field:=9, Criteria1:="<>"
    
    Selection.AutoFilter Field:=4, Criteria1:=strFilter

Thanks

SJ