[RESOLVED] Autofilter Criteria limitation / error
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?
Code:
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
Re: Autofilter Criteria limitation / error
Quote:
Originally Posted by 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?
Code:
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.
Re: Autofilter Criteria limitation / error
Quote:
Originally Posted by koolsid
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?
Re: Autofilter Criteria limitation / error
Quote:
Originally Posted by Starbucks Junkie
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 :)
Re: Autofilter Criteria limitation / error
Quote:
Originally Posted by koolsid
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.
Re: Autofilter Criteria limitation / error
Quote:
Originally Posted by 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?
Code:
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