Macro filter multiple criterea

T

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
 
G

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
 
J

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
 
D

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top