View Full Version : Solved: Autofilter for multiple condition

05-06-2008, 07:46 AM
Hi, I need help with filtering column with followiing conditions. I received following code from recording macro but this somehow doesn't seem to be right. Please note that my table heading is located at Row 5.

1) From Column I, select "In-service" AND "Temporarily Out of Service"
2) From Column H, select "*U/G"
3) From Column M, select "FRP"
4) From Column Q, select "None" AND "vaule w/access"

Recording the macro doesn't seem to be right for item 2) and 4). Can someone please edit the code? Thanks.

Selection.AutoFilter Field:=7, Criteria1:="=1. In-Service", Operator:= _
Selection.AutoFilter Field:=6, Criteria1:="=*U/G*", Operator:=xlAnd
Selection.AutoFilter Field:=11, Criteria1:="6. FRP"
Selection.AutoFilter Field:=15, Criteria1:="=0. None", Operator:=xlAnd

End Sub

Bob Phillips
05-06-2008, 08:33 AM
This seems to work

With Selection

.AutoFilter Field:=6, Criteria1:="=*U/G*", Operator:=xlAnd
.AutoFilter Field:=7, Criteria1:="=1. In-Service", _
Operator:=xlOr, _
Criteria2:="=2. cc"
.AutoFilter Field:=11, Criteria1:="=6. FRP", Operator:=xlAnd
.AutoFilter Field:=15, Criteria1:="=None", Operator:=xlAnd
End With

05-06-2008, 08:55 AM
pl. ignore this! and check the next one.

05-06-2008, 08:56 AM
i) From Column I, select "In-service" AND "Temporarily Out of Service": To select these 2-criteria from column I, should I put the line in code as follows?
AutoFilter Field:=7, Criteria1:="=1. In-Service", _
Operator:=xlOr, _
Criteria2:="=2. Temporarily Out of Service"
ii) What does "Field 7" mean? Is it signifying Column I?

Bob Phillips
05-06-2008, 09:22 AM
i) Yes that is correct

ii) Field refers to the column within the autofiltered columns. From you original code I deduced that you had an autofilter on all columns C:Q (at least). column I is the 7th column within this column set (C is1, D is 2, E is 3, etc.).

05-06-2008, 11:48 AM
hi xld,

thank you very much for the code and the explanation. it works for me. your help is very much appreciated :)