filter by more than 2 criteria

[Alex]

I'm applying the following filtering:
Worksheets("Sheet1").Select

Selection.AutoFilter Field:=1, Criteria1:="=ERP", Operator:=xlOr, _
Criteria2:="=PM" ' Working well

But, I need more criterias there such as:

Selection.AutoFilter Field:=1, Criteria1:="=ERP", Operator:=xlOr, _
Criteria2:="=PM" ', Operator:=xlOr, Criteria3:="=HPW" , _
Operator:=xlOr, Criteria4:="=OJT", _
Operator:=xlOr, Criteria5:="=SBR", _
Operator:=xlOr, Criteria6:="=LD", _
Operator:=xlOr, Criteria7:="=CFT"

How could I do it?

Thanks

 

[Ron de Bruin]

Hi Alex

You can use Advanced Filter to do this
http://www.contextures.com/xladvfilter01.html

Or insert a helper column and AutoFilter on that column
=OR(A1={"A","B","C","D","E"})

EasyFilter have 5 options
http://www.rondebruin.nl/easyfilter.htm

 

[Guest]

I'm applying the following filtering:
Worksheets("Sheet1").Select

Selection.AutoFilter Field:=1, Criteria1:="=ERP", Operator:=xlOr, _
Criteria2:="=PM" ' Working well

But, I need more criterias there such as:

Selection.AutoFilter Field:=1, Criteria1:="=ERP", Operator:=xlOr, _
Criteria2:="=PM" ', Operator:=xlOr, Criteria3:="=HPW" , _
Operator:=xlOr, Criteria4:="=OJT", _
Operator:=xlOr, Criteria5:="=SBR", _
Operator:=xlOr, Criteria6:="=LD", _
Operator:=xlOr, Criteria7:="=CFT"

How could I do it?

Thanks

The way it's been done since the beginning of computer science is to sort on the
least significant field first. Then do it again with the next least, and so
forth until you're done. This is pretty much what any sorting software you'll
find will do under the covers to handle multiple keys.

Bill

 

[Alex]

Thanks a lot, Ron.

Could you please explain how to create this helper column.

 

[Ron de Bruin]

Hi Alex

For example your data range is A1:G100
Row 1 are headers and you want to check A2:A100 for the Criteria

In H1 enter header text
In H2 copy this formula and copy it down to H100
=OR(A2={"ERP","PM","HPW","OJT","SBR"","LD","CFT"})

Now you can use AutoFilter on column H for True or False

 

[Ron de Bruin]

Hi Alex

I have a typo in the formula, this one is OK (one " to much in the other one)

=OR(A2={"ERP","PM","HPW","OJT","SBR","LD","CFT"})

 

[Alex]

Thank you very much, Ron.
It should work but I just cannot handle with the formula. It says "The
formula you typed contains an error". I've tried Ctrl+Shift+Enter but it
doesn't help.

 

[Alex]

Thank you very much again, Ron.
It's working just great.

 

[CLR]

Hi Ron.........

I had a similar problem today, only I needed 20 or's on the Autofilter. I
dug this solution of yours out of the archives and it worked swell........bet
you never thought that when you offered it on 9/1/05 that you would actually
solve a problem for someone 2 years later.........

Thanks,
Chuck, CABGx3

 

[Ron de Bruin]

Hi Chuck

Great to read that I did something good 2 years ago <vbg>

Google is your best friend

 

[Ron de Bruin]

You can also use a range with all values

See the example here (last autofilter example)
http://www.rondebruin.nl/delete.htm#AutoFilter

 

[CLR]

Hi Ron..........
I love this idea, because it will allow the user to edit the list......but I
can't seem to get it working...........

For Each cell In CriteriaRng
With Sheets("importeddata")
'Firstly, remove the AutoFilter
.AutoFilterMode = False
'Apply the filter
.Range("A1:A" & .Rows.Count).AutoFilter Field:=31,
Criteria1:=cell.Value

It fails in my XL97 on this line.......saying "Autofilter method of range
class failed"

Help please....

Vaya con Dios,
Chuck, CABGx3

 

[Ron de Bruin]

This will only work if you want to delete/hide rows
Is that what you want to do ?

If you only want to filter you can use Advancedfilter and use the range as your criteria
Important: Use the same header as your filter column

See Debra's site for more help
http://www.contextures.com/xladvfilter01.html

 

[CLR]

Ah so..........thank you very much kind Sir............
I think I'll just stick with your Plan-A, since it worked so easily.

Vaya con Dios,
Chuck, CABGx3