+ Reply to Thread
Results 1 to 6 of 6

Shortcut/Macro to tick next entry in auto-filter list with multiple filters being used

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

    Shortcut/Macro to tick next entry in auto-filter list with multiple filters being used

    This situation is a bit of an extension to the solution to a similar thread here: https://www.excelforum.com/excel-gen...lter-list.html

    My situation is very similar but my sheet has many more headings and 3 filters in play. The other 2 filters are manually operated, but I would like to achieve the functionality of the auto filter macro linked to above on the main one that is quite repetitively ticked and unticked.

    Try as a I may, I cannot get it to work. Because of so many headings, I reference BZ rather than AA as in the original solution, and my data headings are on row 8 rather than row 1, and the filter in question is column C.

    The 2nd folter sits in either column D or E but is always headed 'SM'; The 3rd filter usually sits around AO to AT but is always headed 'Checker'. There are many sheets I would like to use this on, so intend to put this is in my personal macros for use on all sheets of similar makeup.

    Here is the code that I've borrowed from JBeaucaire who solved the original thread and that i've tried to adapt to suit:

    Please Login or Register  to view this content.

    Any help to adapt this would be greatly appreciated

    Thanks,

    T

    Similar workbook attached
    Attached Files Attached Files
    Last edited by davesexcel; 04-09-2020 at 04:32 AM.

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

    Re: Shortcut/Macro to tick next entry in auto-filter list with multiple filters being used

    1) Your attached workbook does not have any data in BZ as you keep mentioning, so based on your workbook:

    -- column C (Description) - what do you want the macro to do here?
    -- column E (SM) - what do you want the macro to do here?
    -- column AI (Checked) - what do you want the macro to do here?
    _________________
    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
    Registered User
    Join Date
    09-12-2012
    Location
    Preston
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Shortcut/Macro to tick next entry in auto-filter list with multiple filters being used

    Hi JBeaucaire, I really appreciate the reply. I have arthritis so find my hand hurts after repeatedly ticking and unticking these filters! Sometimes I wish it would respond to voice! But until then, your macro is the best thing I've found it can be modified!

    I may be mistaken but I thought that your code used column AA as a temporary spot to place data in order for the macro to function.

    I don't understand the macro so I'm sorry if I gave you a red herring. BZ isn't important to me as there will never be any data sitting out there

    I have filters across all columns, but when using this macro I will be manually setting the filters in E and AI, before I work through each individual tick box in C. So I would like the macro to leave E and AI on the filter they happen to be on at the time (these filter settings could vary), whilst cycling through the filters in column C at each press of the button. The 'show all' button would be useful if it leaves E and AI set but shows all of Col C.

    To complicate matters, it may not necessarrily be E and AI that are the 2 filters in operation as well as it can vary. If that makes a solution unworkable then I will look to find a way of standardising the sheet I am working on as a workaround so that your macro can be used.

    Thanks again for your help,

    T

  4. #4
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,491

    Re: Shortcut/Macro to tick next entry in auto-filter list with multiple filters being used

    I don't have a ready solution for this. My method of collecting the values in column C to use as a list using AdvancedFilter instantly removes the current Autofilter settings completely on the entire sheet. My original solution isn't compatible with preserving any other previously applied filters.

    I'll tug some other sleeves to see if anyone else has any quick ideas.

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

    Re: Shortcut/Macro to tick next entry in auto-filter list with multiple filters being used

    Quote Originally Posted by JBeaucaire View Post
    I don't have a ready solution for this. My method of collecting the values in column C to use as a list using AdvancedFilter instantly removes the current Autofilter settings completely on the entire sheet. My original solution isn't compatible with preserving any other previously applied filters.

    I'll tug some other sleeves to see if anyone else has any quick ideas.
    Thank you, that's very kind. I've been trying to think of ways myself but all roads lead back to those 2 other filters I need to use!

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

    Re: Shortcut/Macro to tick next entry in auto-filter list with multiple filters being used

    Quote Originally Posted by JBeaucaire View Post
    I don't have a ready solution for this. My method of collecting the values in column C to use as a list using AdvancedFilter instantly removes the current Autofilter settings completely on the entire sheet. My original solution isn't compatible with preserving any other previously applied filters.

    I'll tug some other sleeves to see if anyone else has any quick ideas.
    What if I standardised the sheet so that we knew which specific columns had a filter on and what they were set to? The column two to the right of the main filter is always set to view all except 'x'. The others being visible are always 'y', blanks, and a heading, usually 'hide'. I could standardise 'hide' too so that it is always that.

    I use that column to put a cross in the rows I've checked and so when the next description is selected, the last ones checked disappear from view.

    Finally the far most right column, lets say col AT, is always filtered to view the header 'checker', cell value 'check' and blanks. Unticked is always cell value hyphen '-'.

    With the macro first removing all filters before it does its work, would it be possible to add a step to apply the other two filters in the other specified columns in the same step as the main filter is being applied?

    Thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Excel 2007 : Shortcut/Macro to tick next entry in auto-filter list
    By starbearer in forum Excel General
    Replies: 13
    Last Post: 04-04-2020, 05:13 AM
  2. Apply multiple filters based on custom filter form [macro?]
    By Speshul in forum Access Tables & Databases
    Replies: 1
    Last Post: 12-05-2016, 05:25 PM
  3. Replies: 4
    Last Post: 04-29-2015, 02:00 PM
  4. Drop down list whit tick (like filter in 2007 office)
    By arn0ldas in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-13-2014, 08:00 AM
  5. List with auto filters
    By lostinformulas in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-14-2006, 01:33 PM
  6. [SOLVED] filters how do i keep the display list empty until filter applied
    By Beewfc in forum Excel General
    Replies: 1
    Last Post: 04-10-2006, 08:15 AM
  7. data entry for auto filter
    By Parry in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-14-2005, 06:00 AM

Tags for this Thread

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