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
0
Utf
12/10/2009 12:11:01 PM
excel.programming 6508 articles. 2 followers. Follow

5 Replies
7636 Views

Similar Articles

[PageSpeed] 10

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
0
Utf
12/10/2009 12:34:01 PM
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
0
Utf
12/10/2009 12:45:01 PM
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
1
Dave
12/10/2009 1:56:06 PM
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
> .
> 
1
Utf
12/10/2009 3:41:01 PM
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
-1
Dave
12/10/2009 3:43:18 PM
Reply:

Similar Artilces:

multiple CD's, same bank
I've got multiple CD's opened up at the same bank. They are all under the same account number, but each under a separate heading. Each of the CD's mature at a different time, and they will be rolled over into new CD's over the next 18 years. Futhermore, my bank provides online banking services directly within MS Money, including automated download of statements. So...what would be the best way to set up these accounts in Money? Should I create one account named CD and then make a cash transaction for each CD? Should I set up each CD as an individual investment accou...

How OMPM Scanner (offscan) Filter by Access/Modified Date ?
Hello, I have problem to inventory excel files on very big file server, but I believe there are so many documents we no longer need to maintain. I want to skip files if the access date or modified date longer than 6 month, but don't see the OMPM providing feature about it. I currently running OMPM since 2 weeks ago and running out of time for reporting to my manager. Please help me, this is my critical assignment. -- Eldi Munggaran ...

Creating a print-macro that'll only print page 1?
Hi, I have a spreadsheet that contains multiple sheets. I want to add a button that runs a macro, which only prints page 1 on the first sheet (called Scorekort). The "Scorekort" sheet contains 13 pages in total, but I only want to print the first page. Any ideas for this macro? Anders hi, Sub Macro1() Sheets("Scorekort").PrintOut From:=1, To:=1, Copies:=1, Preview:=True, Collate:=True End Sub if you don't want a print preview, Preview:=False -- isabelle Le 2011-05-21 04:16, Anders M a �crit : > Hi, > > I have a spreadsheet that contains multiple ...

outlook 2003sp3: applying the inbuilt junkmail filter on existing messages?
hi all, would like to apply the outlook junk mail filter, with settings set to 'high', on emails already in my inbox. is there any way of doing this? apologies if this has been posted before, I've made a couple of searches but can't find anything... many thanks! No, there's no way to apply the spam filter to already downloaded items. <spam@redo.net> wrote in message news:1137601837.257355.134230@g43g2000cwa.googlegroups.com... > hi all, > > would like to apply the outlook junk mail filter, with settings set to > 'high', on emails already in my...

get rid of macro option at startup
I created and then deleted a macro in an Excel file. Why do I still get the warning box (i.e. the one asking if I want to enable or disable macros) when I open the file? Is there a way to get rid of this since I no longer have macros in this file? Thanks. Brad You need to delete the empty module.! Bill K "Brad Rench" <brench@comcast.net> wrote in message news:kSOdnb0Jjst9afaiXTWJkg@comcast.com... > I created and then deleted a macro in an Excel file. > > Why do I still get the warning box (i.e. the one asking if I want to enable > or disable macros) when I o...

Downloading multiple messages
All of a sudden outlook has started downloading multiple messages from my pop account (ie 22 of the same) It is also sending out 22 of the same message. How can I resolve this issue? What version of Outlook do you have? Does it download duplicates of *all* messages, or just of one or two messages? -- Jeff Stephenson Outlook Development This posting is provided "AS IS" with no warranties, and confers no rights "GW" <anonymous@discussions.microsoft.com> wrote in message news:005101c3bf4c$e748bf30$a501280a@phx.gbl... > All of a sudden outlook has started do...

One Entry to Multiple Rows
I have data that looks like this: X1 | Y1 Y2 Y3 Y4 X2 | Y4 Y5 Y6 Y7 And I need to get to: X1 | Y1 X1 | Y2 X1 | Y3 X1 | Y4 X2 | Y4 ...... etc. I can change the 2nd row's entries to more columns, but that doesn't seem to get me much closer to the needed format (and there are thousands of lines so I'd rather not do it manually). Any ideas? should do it. change mc to suit '===== Option Explicit Sub lineemup() Dim mc As Long Dim mr As Long Dim i As Long Dim lc As Long mc = 3 'col c mr = 1 For i = 1 To Cells(Rows.Count, mc).End(xlUp).Row lc ...

Solver add-in and macros
I just got a new computer with XP and Excel 2003 and tried to run a macro that used to SOLVER add-in function and got a compile error. As a test I recorded a macro using the SOLVER and got the same result when I tried to run that macro. The Compile Error is "Sub or Function not defined". On the Knowledge Base it says I need to add SOLVER to the list of References in the VB Editor. When I go to the references list, SOLVER does not appear. When I open Help for VB and search for SOLVER, it finds nothing. What is going on? Why doesn't VB even recognize the existence ...

Somehow I created a Macro in a worksheet.
I created a macro in an Excel worksheet somehow. I didn't try to, it just happened. Now everytime I open that workbook, it asks me if I want to run the macro, disable it, etc. How the hell do I get rid of the macro? It doesn't show up under tools, macros. And it apparently doesn't do anything either because I can disable it and nothing different happens. Who invented this system anyway? Thanks, V When you record a macro, a module is created to store the macro code. There are instructions here for removing the module that is causing the prompt to appear: http://www.c...

Intelligent Message Filter Installation Failure
I've tried installing the Intelligent Message Filter on a Windows 2000 SP4 Exchange 2003 SP1 server. The installation fails at around 98% with the following error: There is a problem with this Windows Installer package. a program run as part of the setup did not finish as expected. contact your support personnel or package vendor. I tried downloading it again, and had the same results. Any ideas? Thanks ...

Excel 2003 Copy/Paste filtered column
I have a filtered column on my spreadsheet. I have copied the column, changed the figures and then tried to paste it back on to the filtered column. It is not copying over the original filtered column but rather over cells that have been filtered out. The worksheet/cells are not protected. What could the problem be? Kind Regards Heather That's the way pasting works. It'll hit the visible and hidden cells. Heather wrote: > > I have a filtered column on my spreadsheet. I have copied the column, > changed the figures and then tried to paste it back on to the filter...

Intelligent Message Filter (IMF) SCL for Junk E-mail being ignored
I have Exchange Server 2003 Enterprise SP1, and I recently downloaded and installed Intelligent Message Filter. In Message Delivery Properties -> Intelligent Message Filtering, I have - Gateway Blocking Configuration SCL 8 - Store Junk E-mail Configuration SCL 8 All users have Outlook 2003 Professional installed. Although I have SCL set to 8 for "Store Junk E-mail Configuration", Messages with SCL 6 and others with SCL as low as 3 are being sent to the Junk E-Mail folder. I am able to see the SCL of the emails because I installed an SCL Column via Forms using th...

Macro's with Ctrl or Ctrl-Shift?
When you record a new macro it asks you what Ctrl key you want to access it with. If you Edit the Properties of a Macro it asks you what Ctrl-Shift key you want to use to access a macro. How can you have more control over this to assign the proper key to a macro? Thanks. Al, I don't know what you mean by "edit the properties of a macro." You can assign a Ctrl or Ctrl-Shift combination (excluding number keys) to macros either when about to record a macro, or later with Tools - Macro - Macros - Options. If by "have more control," you mean keys other than C...

XL 2007: How to find out what keyboard shortcuts I've assigned to macros?
Over the years, I've recorded and written a lot of macros. (Probably time to take a week or two and go over them and delete at least half!) I assigned a keyboard shortcut to a lot of them. I seem to remember that before XL 2007, there was a keyboard organizer that I could go through and review what shortcuts I had already assigned to my macros. I can't find that in 2007, though. Is there an easy way to generate a list of all my keyboard assignments and what macros they go to? Ed I don't recall ever seeing any such keyboard organizer in 2007. Canned from a prev...

Pivot tables and Macros
I was looking to be able to manipulate (ie change selections from the drop downs, not change the fields in the table or anything)and print from a pivot table using a macro. So how do I go about doing that? When I set up a macro by recording the actions that I want to do, I always get an error message when I try to run it as a macro. What specific steps are you recording, and what error message do you get? Can you click the Debug button, and see the line of code that is causing the problem? Dust For Eyes wrote: > I was looking to be able to manipulate (ie change selections from the >...

Junk E-Mail filter unavailable in online mode
We had Outlook 2003 clients on an Exch 2000 server, and, as expected, the Junk E-Mail filter was not available when working in online mode. In cached mode it was working fine. Now, after upgrading the organization and server to Exch 2003, the Junk E-Mail filter is still not available in online mode. According to all the MS documentation this should be working. Any ideas? Thanks kindly, FV All of the documentation that I have know of says that it works on in Cache Mode. Can you give us a link to the documentation that you saw that shows it will work with online profiles? Thanks, -- Matth...

Multiple Language Issues
Hi all, Has anyone successfully manually updated a non-English supported 5.5 to 2003? (The reason for the manual update was a broken admin account.) Mail and contacts were all exported to PST files, and then imported to 2003. However, although the text of the messages is fine (English and Russian), the text for contact names and message subjects gets gorched if they contained any Russian text. Does anyone know the correct sequence to follow to take messages and contacts in Russian (or other languages) from 5.5 to 2003, while retaining the non-English subject lines and contact names? ...

Multiple Sales Transaction for Same Work Order
I am in the process of trying to create a store level report by department, catagory, and item of the moneys received in total by both deposit on work orders and on direct sales that are not work orders. We require a 100% deposit (of a single tender type) on work orders. I then need to break this deposit down by department, catagory and item. Then I must add that to the same break down for direct sales. Thereby getting a total of all moneys received during a specified period for the store across all registers. So basically I am trying to figure out the data structure and org...

Create Excel macro for barcode output
I am looking for suggestions or samples of an Excel macro that will output barcodes that can be printed and then attached onto loan documents. Example: input loan # 555 and print off 20 barcoded labels - each label contains loan number + the document type. hi, you don't need a macro for that. we just got throught barcodeing our part numbers in the ware house and used excel and no macro. bar codes are just another font. excel uses 3 0f 9 barcode font. you can download others off the net. you can format a cell with this font and it will show as barcode on the sheet and numbers in th...

Multiple Report Dictionaries
We just acquired another company and some of the modified reports will need to be different than the ones we currently use. There are a few people that will need to work with both companies in Great Plains. Is there a way to change which reportws dictionary a company uses - or a user uses - without editing the launch file? Thanks,, Mike You could have two installations of the client on the workstation. Each installation would have a different dictionary file. The user would have to pick the right one. "MikeW" <MikeW@discussions.microsoft.com> wrote in message news:...

in Publisher I want to merge/send email with multiple attachments
Using Publisher 2007 I know how to do a merge and then email out from a list. Publisher gives the option to attach multiple attachments to the email but when the send actually goes out it only carries the first of the attachments and discards the others. Is there a trick to making multiple attachments stay attached? or is this a bug... Thanks to anyone who can clear this up.. Rust Gilbert Rust, Since you already know how to do email merges in Publisher 2007, you're probably the ideal person to answer my question...I have been trying to do an email merge using an existing publis...

Removing filters from data
Hi. I have recorded a macro to remove filters from data lasts in advance of performing other actions. However if the data is unfiltered the macro falls over with the message Run time error '1004' ShowAllData method of Worksheet class failed. I think I need some sort of if error continue code or something to check filtering first. I would be grateful if someone could point me in the right direction please. Hi Philip Try If Activesheet.Filtermode Then ActiveSheet.ShowAllData -- Jacob "Philip J Smith" wrote: > Hi. > > I have re...

Can I embed or link multiple .pdf files into or to an excel file?
I would like to link .pdf files to an excel file. Within my excel file I would like to have a column that has file names in it. Then have excel link those files to the excel file so when I print the excel file all the linked files print along with it. use hyper link "GrubbyG" wrote: > I would like to link .pdf files to an excel file. Within my excel file I > would like to have a column that has file names in it. Then have excel link > those files to the excel file so when I print the excel file all the linked > files print along with it. ...

Sending multiple Emails so each person does not get the list
We want to send out multiple emails to several email accounts. We dont want the accounts to see the list of accounts that the email went to. How does one do this in Outlook? In any contacts folder, Tools | Mail Merge is the best choice. --=20 Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/olconfig/index.htm and Microsoft Outlook Programming - Jumpstart for=20 Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx =20 "tom" <Spamblocker@ameritech.net> wrote in message = ...

Command Button to Sort and Filter by Selection
Is it possible to create a command button on a form that will allow you to sort records or to use the Filter by Selction option? They are not choices I can select while using the wizard or the macro builder. However, both of these would be extremely useful as I'm building a database for someone who has never used a database before and isn't all that comfortable with computers in general. I am using Access 2003 and I am NOT familiar with Access Visual Basic, Java or building macros from scratch. I'm sure there is a way to use these tools to create what I want, ...