Consulting

Results 1 to 8 of 8

Thread: How do I get Autofilter to select the next criteria?

  1. #1
    VBAX Newbie
    Joined
    Aug 2007
    Posts
    3
    Location

    Lightbulb How do I get Autofilter to select the next criteria?

    I'm just a beginner at this and am not sure how to proceed. What I need is to get the autofilter to automatically select the next criteria, without actually specifying what the criteria is, until the end of the criteria list.

    i.e. in the example below, instead of Criteria1:=B, Criteria1:=C, etc, as the next criteria, I would like it to just go on to the next criteria in the list until the list runs out.

    This is what i want to use it in.
    HTML Code:
    Sub Autofilter_progressive()
       
         Selection.AutoFilter Field:=10, Criteria1:="A'
         Application.Goto Reference:="R1C5"
         Selection.End(xlDown).Select
         Range("E18:I18").Select
         Selection.Copy
         ActiveSheet.Previous.Select
         Application.Goto Reference:="R2C2"
         Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _         False, Transpose:=False
         Range("B3").Select
         ActiveSheet.Next.Select
         Selection.AutoFilter Field:=10, Criteria1:="B"
         Application.CutCopyMode = False
         Selection.Copy
         ActiveSheet.Previous.Select
         Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _         False, Transpose:=False
         Range("B4").Select
         ActiveSheet.Next.Select
         Selection.AutoFilter Field:=10, Criteria1:="C"
         Application.CutCopyMode = False
         Selection.Copy
         ActiveSheet.Previous.Select
         Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _         False, Transpose:=False</p>     Range(&quot;B5&quot;).Select
         ActiveSheet.Next.Select
         Selection.AutoFilter Field:=10, Criteria1:=&quot;D&quot;
         Application.CutCopyMode = False
         Selection.Copy
         ActiveSheet.Previous.Select
         Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _         False, Transpose:=False
         Range(&quot;B6&quot;).Select
         ActiveSheet.Next.Select
      End Sub

    Looking forward to the solution.

    Thanks

    Last edited by Star Key; 08-28-2007 at 11:35 PM.

  2. #2
    Mac Moderator VBAX Expert shades's Avatar
    Joined
    May 2004
    Location
    Kansas City, USA
    Posts
    638
    Location
    Howdy, and welcome to the board.

    Just a question: Is this specific to the Mac version of Excel, or general Excel? The reason I ask, is that if it is the latter, I would move it to the Excel forum for broader exposure and better chance of receiving answers.

    Software: LibreOffice 3.3 on Mac OS X 10.6.5
    (retired Excel 2003 user, 3.28.2008 )
    Humanware: Older than dirt
    --------------------
    old, slow, and confused
    but at least I'm inconsistent!

    Rich

  3. #3
    VBAX Newbie
    Joined
    Aug 2007
    Posts
    3
    Location
    Thanks Shades. This is Mac specific.

  4. #4
    VBAX Mentor tpoynton's Avatar
    Joined
    Feb 2005
    Location
    Clinton, MA
    Posts
    399
    Location
    a sample workbook would be helpful, and not to be disrespectful, but I'm not seeing anything mac specific in that code. I'd recommend posting/moving in the regular excel forum. in fact, there are probably folks there who could answer the question without the sample workbook

  5. #5
    VBAX Newbie
    Joined
    Aug 2007
    Posts
    3
    Location

    How do I get Autofilter to select the next criteria? Excel 2004/Mac

    tpoynton, while there is nothing mac specific in my code my concern was that the answer would contain code that does not work on my mac, e.g. sendkeys does not work on my mac with the version of excel/vba that I have. But I guess it won't hurt to put it out there. What's the best way to move to the regular excel forum?
    Last edited by Star Key; 09-04-2007 at 05:31 PM.

  6. #6
    VBAX Mentor tpoynton's Avatar
    Joined
    Feb 2005
    Location
    Clinton, MA
    Posts
    399
    Location
    I'd recommend posting a new post in the regular Excel forum, with a link to this post. mention that you are using a Mac, and the Excel version you are using. if you come up with a solution there that doesnt work on the Mac, then come back here with the specifics; i've always been able to find a workaround, but I have to say that the vast majority of VBA works on both Mac and PC's. The Mac VBA help does a pretty good job of explaining what works and doesnt in Mac, too.

    Also, to help improve the readability of your posts, use the VBA tags [vba] [/vba]

    I think a bigger issue than mac-specific code is the fact that Mac VBA tends to be a version behind Windows.

  7. #7
    Just a question: Is this specific to the Mac version of Excel, or general Excel? The reason I ask, is that if it is the latter, I would move it to the Excel forum for broader exposure and better chance of receiving answers.
    Flyer maker




  8. #8

    mac version of excel

    Hi, I am manomehar
    I read your thread If you utilize Microsoft surpass on your mac, you'll be able to save the spreadsheets you produce and open them in Numbers, Apple's computer programme app. it is a handy feature to use. This is the very best thing.
    Last edited by manomehar; 01-10-2019 at 12:48 AM. Reason: i found some mistakes thats why i edit this post

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •