Macro filter multiple criterea

[TooN]

Hello,

I've been searching the forum for a while but i cant find a right solution.
I have this macro:

Sub filter()

Sheets("KAM").Select
Selection.AutoFilter Field:=12, Criteria1:="=ANLAGE", Operator:=xlOr, _
Criteria2:="=PE", Operator:=xlOr, _
Criteria3:="=ED03"
End Sub

I would like to have a macro that filters multiple criterea (e.g. "ANLAGE",
"PE", "ED03"... etc) The first two was no problem but when i wanted to add a
third one and a fourth one it gives an error.

In the end i would like to have two buttons that are attached to the macro.
When clicking the button it show different filtered lists

Thanks

 

[Gareth]

TooN,

You cannot have more than two criteria with Autofilter, I would use the
following:

Sub filter()
Sheets("KAM").Select
Range("A1:M30").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:=Range("N1:N4"), Unique:=False
End Sub

Range("A1:M30") would be the extent of you data and Range("N1:N4") would
contain you column heading and criteria (N1 would be the column heading in
column 12, N2 would be ANLAGE, N3 would be PE and N4 would be ED03).

Not quite sure what you mean by having two buttons though.

Hope this helps.

Gareth

 

[Jacob Skaria]

Suppose your data in active sheet..Data range is ColA/ColB A1:B10..Filter
criteria
range is C1:C3 (with header)

Col A Col B Col C
Header 1 Header 2 Header 1
a 1 a
a 2 b
b 3
a 4
b 5
c 6
d 7
e 8
a 9

Sub Macro2()
Range("A1:B10").AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=Range("C1:C3"), Unique:=False
End Sub

 

[Dave Peterson]

You could add another helper column that contains a formula that evaluates to
true/false. Then filter on that column.

The formula could be as simple as something like:

=or(a2="hi",a2="bye",a2="there")
or this equivalent:
=or(a2={"hi","bye","there"})

Or it could be as complex as you need.

Or you could learn about advanced filtering and criteria ranges.

I'd start at Debra Dalgleish's site:
http://contextures.com/xladvfilter01.html

 

[ryguy7272]

Have you ever seen this tool?
http://www.mediafire.com/?5tmfjngnymz

Type some criteria in row 1 and the tool will auto-filter. That may give
you some ideas.

HTH,
Ryan--

 

[Dave Peterson]

Nope. But I don't download or open files from sources I don't know.