Closed Thread
Results 1 to 14 of 14

Excel 2007 : Shortcut/Macro to tick next entry in auto-filter list

  1. #1
    Registered User
    Join Date
    08-08-2011
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    4

    Shortcut/Macro to tick next entry in auto-filter list

    Hi,

    I'm using Excel 2007 and constantly working with lists with auto-filter applied. However, a lot of my work involves:

    -Selecting the drop-down arrow to display the filtered entries
    -Remove the tick from a previously filtered data
    -Add the tick to the next entry and then filter the data

    I'm doing the above actions with the mouse but since the filtered list has more than a 100 entries, I'm looking for a shortcut or macro to automate the above process. Can anyone please help me out?

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Shortcut/Macro to tick next entry in auto-filter list

    Are you talking about moving through all the unique items in a single column using the AutoFilter?

    Are these items a set array of values or are they completely random?

    I've written things like this in the past to click through and filter a dataset by all the items in a single column, one at a time, but the column has always been filled with known values... like employee names or sales categories. You get the idea.

    To work best, the list of unique filter options you'd like to see would be listed somewhere, perhaps a separate (hidden?) sheet?

    If you posted a slimmed down version of your workbook, it would be easiest to show you. Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Shortcut/Macro to tick next entry in auto-filter list

    Here's a link to my sample sheet from a previous project:

    http://www.excelforum.com/excel-prog...g-value.html#6

    The NEXT button on this project wouldn't have to work so hard if the items were already listed somewhere, in this instance the filter items could be anything, so it has to create the search list each time it is run, taking longer.
    Last edited by JBeaucaire; 08-08-2011 at 11:28 AM.

  4. #4
    Registered User
    Join Date
    08-08-2011
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Shortcut/Macro to tick next entry in auto-filter list

    Hi,

    Thanks for your reply. I'm afraid I didn't explain myself clearly. Refer this screenshot:

    excel.JPG

    Currently the data is filtered for "Alice Mutton". I'm looking for a way (shortcut/macro) to:

    -Drop down the auto-filter list
    -De-select Alice Mutton
    -Select the next one i.e. Aniseed Syrup

    And subsequently repeat the process for "Boston Crab Meat", "Camembert Pierrot", etc.

    TIA

  5. #5
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Shortcut/Macro to tick next entry in auto-filter list

    O ye of little faith. I do believe I understood and offered a usable macro, or at least a usable starting point.

    Auto - Autofilter

    This macro DOES apply an autofilter for each item in the selected filtering column. Although the macro written is for 2003, it should work just fine in 2007. Download the sample sheet from that page, drop your sample data onto the sheet and give it a try. Each time you click NEXT you will defilter the prior value and select the next.
    Last edited by JBeaucaire; 08-08-2011 at 11:35 AM.

  6. #6
    Registered User
    Join Date
    08-08-2011
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Shortcut/Macro to tick next entry in auto-filter list

    Excellent! That indeed is a very useful starting point and is what I'm looking for. I figured out that AFCol needs to be changed in the macro to rotate a different column.

    Pasting the data into Auto-autofilter.xls works perfectly. However, how do I export this into a different sheet. When the Auto-Autofilter.xls sheet is open, I'm able to run this macro in another sheet but I don't see the Next and Show All buttons in the new sheet. Any suggestions on how to get them?

    Thanks so much and apologies for showing little faith in my earlier post

  7. #7
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Shortcut/Macro to tick next entry in auto-filter list

    1) You'll have to open the VBEditor and copy the macro from my standard module into a standard module in your project.

    2) For buttons, you'll have to open the FORMS toolbar and draw buttons on your sheet. When you draw a FORM button on a worksheet, it immediately pops and asks you to assign a macro to it, easy-peasy.

  8. #8
    Registered User
    Join Date
    08-08-2011
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Shortcut/Macro to tick next entry in auto-filter list

    Brilliant! Thanks Jerry :-)

  9. #9
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Shortcut/Macro to tick next entry in auto-filter list

    If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

  10. #10
    Registered User
    Join Date
    03-24-2017
    Location
    Brighton
    MS-Off Ver
    Excel 2010 & 2013
    Posts
    3

    Re: Excel 2007 : Shortcut/Macro to tick next entry in auto-filter list

    Amazing! This is exactly what I was looking for, thank you so much.

    I've almost got it working on my spreadsheet by changing 'AFCol' to 16 as I'm filtering column 'P'
    But my data starts on row 7 (the headers) not 1.

    What part of the code would I need to amend to getting it working?

    Many thanks in advance
    Vicky

  11. #11
    Registered User
    Join Date
    03-26-2016
    Location
    India
    MS-Off Ver
    2007
    Posts
    1

    Re: Shortcut/Macro to tick next entry in auto-filter list

    Hi,
    This code works in excel. But It always filter value in 2nd Column. I want this to be in third.
    Please help me out !!

  12. #12
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Excel 2007 : Shortcut/Macro to tick next entry in auto-filter list

    Hi Arjun. Use the macro linked in post #5.

    At the top of that macro you see a variable AFCol which stands for "AutoFilterColumn" and in the published macro it is set to = 2 which means column 2. You can change that 2 to a 3 and it will then work based on column C. Etcetera.

    This is not a new question for this existing macro, it qualifies as a clarification on understanding/using the macro as it already exists. If you ultimately need additional functionality other than simply picking which column, that would be new and need to go in a new thread of your own.

  13. #13
    Registered User
    Join Date
    09-12-2012
    Location
    Preston
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Excel 2007 : Shortcut/Macro to tick next entry in auto-filter list

    Hi J, I've come across your solution and have the same problem as vwhite26: My data header is on Row 6 and col 3 is the column I'm working with. I also have headers all the way along to col AA so have changed that refererence to 'BZ'.
    I still cannot get it to use row 6 as the header. If not too hampered by the coronavirus, would you mind taking a look? I just need clarification on how it selects the header row. Here is your code that I've butchered to try to suit my use case:

    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    09-12-2012
    Location
    Preston
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Excel 2007 : Shortcut/Macro to tick next entry in auto-filter list

    Because my problem has now evolved to more than just understanding the posted macro, I've started a new thread here for anyone interested:


    https://www.excelforum.com/excel-gen...eing-used.html
    Last edited by JBeaucaire; 04-06-2020 at 01:57 PM.

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1