VBA AutoFilter how to apply 2 ranges for selection to copy

Using XL 2003 & 97

Currently the code below works fine.  It selects values equal to or greater 
than 20000 but less than 38999.

    Cells.AutoFilter Field:=3, Criteria1:=">=20000", Operator:=xlAnd, _
        Criteria2:="<38999"
    Range("A1").Select
    ...... (copy paste routine)

What is the most efficient syntax to convert the above code to handle:
  ">=20000 and =<35000"
          AND
  ">=35500 and <38999"

TIA

Dennis
0
Dennis (299)
11/10/2004 11:47:03 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
317 Views

Similar Articles

[PageSpeed] 53

I think you've got trouble.

First, autofilter can only have 2 criteria.

And secondly, I don't think any number will be true for all 4 of your values. 
(So I'm guessing you meant OR between the two sets of criteria.)

Do you know about Data|Filter|Advanced filter?

You can give it a criteria range and use that to show only the records that
match.

Debra Dalgleish has some notes at:

http://www.contextures.com/xladvfilter02.html

But say your Field3 header was named QTY.

You could put this in an unused portion of your workbook:

QTY       QTY
>=20000  <=35000
>=35000  <38999


That's right 3 rows and 2 columns.  That advanced filter criteria range will
treat the stuff on the same row as "AND" and use "OR" for the second row.

Another option that I'd use.

Put in a helper column:

=OR(AND(C2>=20000,C2<=35000),AND(C2>=35000,C2<38999))

And copy down the range of rows.

Then use that in your filter.



Dennis wrote:
> 
> Using XL 2003 & 97
> 
> Currently the code below works fine.  It selects values equal to or greater
> than 20000 but less than 38999.
> 
>     Cells.AutoFilter Field:=3, Criteria1:=">=20000", Operator:=xlAnd, _
>         Criteria2:="<38999"
>     Range("A1").Select
>     ...... (copy paste routine)
> 
> What is the most efficient syntax to convert the above code to handle:
>   ">=20000 and =<35000"
>           AND
>   ">=35500 and <38999"
> 
> TIA
> 
> Dennis

-- 

Dave Peterson
0
ec357201 (5290)
11/10/2004 11:41:28 PM
Reply:

Similar Artilces:

Compare and Merge #2
I know that I cannot compare and merge a protected workbook but if I make a copy of the data in the spreadsheet and save it with a differnt name, it stills doesn't allow me to merge the two. ...

two copies of every email in crm3
Hi Whenever a user sends an email message from within a crm3 account as an activity, two instances of the original message subsequently appear in "history." how do i ensure that only 1 instance of an email is kept? many thanks in advance This does happens if you sent an email from one Crm user to another. If this is the case you can avoid this by going to Settings > Organization Settings > System Settings and on the tab E-mail Tracking set the second radio button (Exclude e-mail) to Yes. If it is not user to user mail the above won't change that. -- Patrick Verbe...

Error 1004 when copying and pasting values
All, A co-worker has just had a macro (which has been working for the las year) crash at the last line of code shown below. The error is 1004 an suggests that the copy/paste areas are not the same, even though the obviously are. Any ideas? Thanks Will Range(Cells(2, 12), Cells(2, 20)).Select Selection.Copy Range(Cells(3, 12), Cells(EndRow, 20)).Select Selection.PasteSpecial Paste:=xlFormulas Calculate Range(Cells(3, 12), Cells(EndRow, 20)).Select Selection.Copy 'breaks here Selection.PasteSpecial Paste:=xlValue -- Message posted from http://www.ExcelForum.com Hi do you have mer...

Form #1 that filters form #2
I have a form #1 customers and form #2 orders. I put a button on form #1 to open form #2 filtered but customerID. However, when I go to data enter the second...tenth customers the order form does not save the information into table or form. What am I doing wrong and How can I correct this problem? On Fri, 13 Nov 2009 08:09:01 -0800, Jennifer <Jennifer@discussions.microsoft.com> wrote: >I have a form #1 customers and form #2 orders. I put a button on form #1 to >open form #2 filtered but customerID. However, when I go to data enter the >second...tenth customers th...

OWA and SSL #2
Hi Just managed to get my Exchange 2000 OWA working and accessible on port 80 thru our firewall. next step is to implement SSL. I want to use my own certificates (cheapos won't let me buy one from verisign!). can someone point me in the right direction to create and install my own certificates plus get owa working with ssl? Advice greatly appreciated. Brett ...

When someone send a mail make a copy in a mailbox.
Hi! How can I do that? I wan to make a copy message in a mailbox, when somebody send a mail to somebody? And I want it with all mailboxes. Thanks in advance Hi, what Exchange version are you using? -- Keep on fighting! Christian Please reply to this message, to let others know whether it was useful to help you solve your problems or not. Original Message: "Corner" <corner@t-email.hu> wrote in message news:uPFJa6hfEHA.3556@TK2MSFTNGP12.phx.gbl... > Hi! > > How can I do that? I wan to make a copy message in a mailbox, when somebody > send a mail to somebody?...

CListCtrl & multiple row selection
How can I enable/disable multiple rows selection from code? Turn the single selection property on or off in the resource editor Miki Peric wrote: > How can I enable/disable multiple rows selection from code? > > -- ___________________________________________ Van Gennep - Media Automation Consulting bv Burg. Stramanweg 105 1101 AA Amsterdam The Netherlands Phone: +31-20-697 6029 Fax: +31-20-697 2249 E-mail: mkools@vangennep.nl WWW: http://www.vangennep.nl WWW: http://www.vangennep.com ___________________________________________ I need it at run time. ...

Error 1004 using array in Sheet select.
If I select a sheet with its real name: Sheets("Sheet1").Select >>>>>- Works fine Sheets(Array("Sheet1", "Sheet2")).Select >>>>>- Works fine If I assign the sheet name(s) to a constant Public Const TSGSheet1 = "Sheet1" Public Const TSGSheet2 = "Sheet2" Sheets(TSGSheet1).Select >>>>>- Works fine Sheets(TSGSheet2).Select >>>>>- Works fine Sheets(Array(TSGSheet1, TSGSheet2)).Select >>>>>- fails with Error 1004 My macro's/V...

Making the "add item" option available to VBA
I'd like to add VBA code to the Project PO Window that automatically checks the "add item" option from the toolbar when the window is opened. Anyone know how to get that menu option available to VBA? thanks! Martha That really isn't possible (directly) with VBA because VBA cannot see menus. About the only thing you can do is record a macro that checks this option. Then run the macro from VBA. that isn't directly available from vba either but there is a TK that tells you how to work around it. "Martha" <Martha@discussions.microsoft.com> wrote in messa...

scheduled deposit #2
is there a way to schedule an automatic deposit on the 4th wednesday of every month? Still no. "mike" <anonymous@discussions.microsoft.com> wrote in message news:0e3101c3fe0f$f85f8540$a601280a@phx.gbl... > is there a way to schedule an automatic deposit on the > 4th wednesday of every month? ...

Event ID: 7010 #2
Hi, I keep getting event ID 7010. Some sort of esmtp extention for exchange server communication? Is there a way to disable this on exchange 2003? thx jason On Wed, 20 Jul 2005 14:25:21 -0600, "jason.sigurdur" <comp.techs@aspenview.org> wrote: >Hi, I keep getting event ID 7010. Some sort of esmtp extention for exchange >server communication? > >Is there a way to disable this on exchange 2003? > >thx jason > http://support.microsoft.com/default.aspx?scid=kb;en-us;555372&sd=rss&spid=1773 ...

Invest in our children
INVESTMENT OPPORTUNITY! Fork It Over! a Ground Floor Unique Dimension & Concept In the Children's Entertainment Market... For More information http://www.kidseyeview.us ...

Apply formatting through code
Hi and TIA. I have a worksheet like so. I'm trying to conditionally format the cells in Column C. If this is possible where do a place a call to the procedure? I want the procedure to run for each individual row except I can't simply copy the formula down the sheet because I have headers and totals rows. I'm new to excel. I'm an Access geek. Any advice or if you can point me in the right direction is appreciated. Thanks for your time! Header: USS Vinson A B C 1 3 3 1 2 2 3 4 3 1 2 2 Tot 6 8 7 ...

Excel - Array Formulas - Freeze
Hi all I have a big problem. It takes about 30 minutes to save my excel fil because I have many array formula: about 32 000! So do you have a ti to accelerate the computing process? Is a VBA function would do i faster? Here is an example of what I use: Col A contain unique numbers Sheet 1: Col A Col B Col C Row1 001 123 9i9 Row2 005 456 8u8 Row3 003 406 8ur etc... to +8000 In sheet 2, I have to check if the data exist in sheet 1. Col D contai Yes (data is in the sheet1) or No... Examples: Col D check if the data in Col B is the same in sheet 1 Col...

2 User Profiles, 1 Outlook Profile?
I am running Windows Server 2003 and Office 2003. I have 2 accounts that require access to the same Outlook data - emails, email accounts, address book, rules, calender etc. Only 1 account will be used at a time though i need outlook to be exactly the same for both profiles. How is this done? ...

Preview #2
How can I turn the preview pane on/off in Outlook? I turned it off, how do I get it back on? Jeff Outlook 2003? View-->Reading Pane Select where you want the pane to show. "Jeff T." <jeftho@nospam.invalid> wrote in message news:Ogu9S180GHA.4284@TK2MSFTNGP04.phx.gbl... > How can I turn the preview pane on/off in Outlook? I turned it off, how do > I get it back on? > > Jeff > It's Outlook 2000 and I got it figured out, I right click a blank area and s...

unable to sort in Select Names box
I just migrated from 98se/OL 2000 to XP proff/OL 2002. Now when I open a new email and click the "To..." button, and it opens the "Select Names" box, and I go to select a recipients email the fields won't let me sort like I used to. Plus there are additional entries with fax numbers, how do get it to sort and NOT display the fax number entries? Also, is there any way I can make this "Select Names" box bigger so I don't have to scroll right to see the if it is the correct email address if they have multiple email address's? Thanks Ted No...

PST File #2
Is there anyway in the PST file to have auto archiving turned off completely? Even better if I could disable it. Also the variable "ModifyDefaultProfileIfPresent=FALSE", if I change that to TRUE, will this update the default profile, rather than delete the old one and recreate it? I.E. turning cached mode on (Not all clients have the cached mode part of the PST) and turn off auto archiving (If I can do that) Thanks, Andrew Here is our current PST file: ; ************************************************************** ; Section 1 - Profile Defaults ; ************************...

Apply cash receipts in a nonfunctional currency
When entering a non-functional currency cash receipt, our client would like to be able to apply a cash receipt to the invoices at the time of cash entry rather than post the receipt and then use the apply option. There are valid reasons as to why this could be an issue, but the user wanted us to post the suggestion. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggest...

remove "Protected Storage System Provider" do not help #2
After updating from Outlook2000 to Outlook XP(2002) OutlookXP keeps asking for passwords for every account :-((( I have tryed the trix "remove Protected Storage System Provider" in the registery (done by the letter as described in two Q-articles) but it do not help or change anything :-((( Please help -- erik Denmark ...

Importing data #2
Importing data into customer, item, suppliers, and GL master files. This is a brand new company and I need to import the master files from another system. I am able to easily dump each file to excel, but how do I import into GP? -- Gino S. GPK Computers Gino, Usually you would use an import tool like Integration Manager or eConnect. If this is a one time import, you're probably better off with Integration Manager. -- Victoria Yudin Dynamics GP MVP "Gino@GPK" <gino@gpk.net.au> wrote in message news:5B6CAB1C-FA16-408A-8242-8B1B9E80B011@microsoft.com... > Impo...

Defrag Question #2
Can I use the Windows disk defragmentation tool to run a defragmentation on the C and D drives of a server that holds our Exchange 2003 mail database? The Server I want to defragment is Windows 2003 Standard Edition, SP 1. Thank you. -- Carolyn What problem are you trying to solve by doing this? see http://www.microsoft.com/exchange/techinfo/tips/defragmentation.asp "Carolyn" <Carolyn@discussions.microsoft.com> wrote in message news:BACD390E-75AE-4C19-8F6F-3BFE1DA41F2E@microsoft.com... > Can I use the Windows disk defragmentation tool to run a defragmentation on &...

Microsoft Money 2004 Deluxe #2
I received a pop-up message that my 2004 version of MS Money Deluxe would expire on 9/1/2007. It said that I would loose online services. I see that the 2007 Version has changed the three year online service capability to two years. Are there any other reduced functions in 2007 deluxe vs 2004 deluxe? Also, I seem to recall that MS Money updates have come out in the summertime in the past. Can anyone verify this? Al Some things in M07 that changed from previous versions: 1) No more multi-select projected balance in Bills. 2) They are trying to hide and then deprecate Advanced Bud...

yahoo e-mail #2
I have just lost my job, and want to get my yahoo e-mail to load automatically into Outlook. I have been unsuccessful. Can you help? You will need a paid yahoo premium account to work in Outlook - see their mail help pages for instructions. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. While thinking hard, suzanne <anonymous@discussions.microsoft.com> queried: | I have just lost my job, and want to get my yahoo e-mail | to load automatically into Outlook. I have been | unsuccessful. | | Can you help? There is a free 3rd party...

Totalling a group of worksheets #2
How do I summarize a continguous series of worksheets on to a summary page? e.g. I have a number of worksheets A,B,C,.......K. I also have a summary worksheet. I want to total cells A1 in the group of worksheets and show the total in the summary worksheet -- Thanks BJ Try this: On the Summary sheet, select the cell where you want the A1 summary to display. Type =sum( Then click on the Sheet A tab Hold the [Shift] key down Click on the last sheet's tab Release the [Shift] key Type ) and press [Enter] The formula should look like: =SUM(A:K!A1) Does that help? *********** Regards...