Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to pull data with criterea for placement on a new sheet | Excel Discussion (Misc queries) | |||
Creating a macro to filter data on multiple sheets | Excel Programming | |||
how do i countif on multiple criterea? | Excel Worksheet Functions | |||
Macro: Filter Multiple header then extract to Multiple Files | Excel Discussion (Misc queries) | |||
Create macro to filter on multiple criteria | Excel Worksheet Functions |