View Full Version : AutoFilter - number of criteria

03-01-2007, 10:14 AM
Hi All,

I was working on trying to filter the data in a spreadsheet which will filter the data for the following conditions:
1. If the value in the column 3 is "P" or "N" or ("N/A" and if the value of column 4 is NOT equal to "some text string").

I was trying the code
Selection.AutoFilter Field:=3, Criteria1:= "=P" , Operator:=xlOr, Criteria2:= "=N", Operator:=xlOr, Criteria3:= "=N/A", Operator:=xlOr
and then writing a similar code for the And condition for field 4. Well, it does not work. I know that I am doing something wrong, but I do not know how many criteria can be added to one line of code. Most examples give 2 and if I add the third I get the error statement.

Finally I worked out the problem by using If.. Then statement.

I want to know whether we can make use of AutoFilter code for such problems, and if we can, what is the code I should follow.

I appreciate any suggestions.

- Krishna

Bob Phillips
03-01-2007, 10:22 AM
You only get 2 criteria.

The way to overcome it is to have a helper column with a formula that tests however many conditions that you want, and filter that for TRUE or FALSE,.

03-01-2007, 12:37 PM
Thanks very much - if I understand correctly it is two criteria for all the columns in the worksheet.

Bob Phillips
03-01-2007, 12:42 PM
Two criteria per field (column).

Zack Barresse
03-01-2007, 01:56 PM
If you're doing this via code, you can set the formula with only a couple of additional lines, then delete when you're done working with it making it look seamless.

03-01-2007, 03:52 PM
Thank you all for the suggestions:yes