Autofilter VB script problem

[jardenp]

Is there a limit to the number of criteria you can use with a filter? I want to filter out any cells that contain "RF" or "53100", or "53101", ... or "53110"

The VB debugger keeps stopping at this when I run my macro:

ActiveSheet.Range("$A$1:$A$1174").AutoFilter Field:=1, Criteria1:="=*RF*", _
Operator:=xlOr, Criteria2:="=*53100*", _
Operator:=xlOr, Criteria3:="=*53101*", _
Operator:=xlOr, Criteria4:="=*53102*", _
Operator:=xlOr, Criteria5:="=*53103*", _
Operator:=xlOr, Criteria6:="=*53104*", _
Operator:=xlOr, Criteria7:="=*53105*", _
Operator:=xlOr, Criteria8:="=*53106*", _
Operator:=xlOr, Criteria9:="=*53107*", _
Operator:=xlOr, Criteria10:="=*53108*", _
Operator:=xlOr, Criteria11:="=*53109*", _
Operator:=xlOr, Criteria12:="=*53110*"

Any suggestions what the problem is?

Thanks!

- Josh in IN

 

[pgc01]

Is there a limit to the number of criteria you can use with a filter?

Hi Josh

If you check the help you'll see that the maximum number of criteria is 2 per field.

For the code your posted you need 3:

...
Criteria1:="=*RF*", _
Operator:=xlOr, Criteria2:="=*5310*", _
Operator:=xlOr, Criteria3:="=*53110*"
...

... but Criteria3 does not exist.

If 2 is not enough for your problem you'll have to rethink your solution.

 

[jardenp]

Thanks pgc! I appreciate the reply and info. The two criteria limit (which is lame!) explains the problems.

I found a work around for the problem. The 53100-53110 are the only unit numbers I need that don't have "RF" at the end, so I just wrote in a find & replace script to add "RF" to each. Now I can just filter for the first criterion of, "contains 'RF'".

Thanks again! I really appreciate the help I've gotten from the posts I've made at this board.

 

[Datsmart]

I would suggest looking at Advanced Filtering to do what you are after.
Good example of Advanced Filter steps here:
http://www.contextures.com/xladvfilter01.html
AdvancedFilter Demo
http://www.contextures.com/xlVideos04.html
Another good link with sample VBA code here:
http://www.meadinkent.co.uk/xlfilter.htm

Advanced Filter allows you to preset filter options.
It also allows you to filter by criteria in ways standard autofilter can not do. You can choose an export location in the dialog box or in VBA code.
Once you setup the code and criteria, you can use a macro button to copy specific data from your main sheet to a "Report" sheet. The original data remains unchanged and the data "Filtered" to another page can be used as desired.

 

[jardenp]

Thank you, Datsmart! That's very helpful info. I will look into that as I have to do work arounds with autofilter frequently. I've never used the advanced filter because the interface didn't immediately make sense to me, but I will go through the tutorial you suggest.

Thanks again!