Macro filter multiple criterea
 

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

Macro filter multiple criterea
 

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

"TooN" wrote:

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

Macro filter multiple criterea
 

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


--
Jacob


"TooN" wrote:

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

Macro filter multiple criterea
 

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

TooN wrote:

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

--

Dave Peterson

Macro filter multiple criterea
 

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--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Dave Peterson" wrote:

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

TooN wrote:

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

--

Dave Peterson
.

Macro filter multiple criterea
 

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

ryguy7272 wrote:

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--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

"Dave Peterson" wrote:

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

TooN wrote:

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

--

Dave Peterson
.


--

Dave Peterson