+ Reply to Thread
Results 1 to 10 of 10

Keeping the filtering criteria

  1. #1
    Registered User
    Join Date
    02-22-2007
    Posts
    47

    Question Keeping the filtering criteria

    Hi,

    I have created a protected sheet, when any of my macros run I remove the autofliter, because it can impact my code and the results - it's a necessity.

    However, it's super engraging actually, because users use the filters extensively and every time they run a macro all their filter criteria is removed and they have to put it back - super cumbursome.

    Therefore, I would like:

    1. Before removing the filter I would like to test the filter criteria.
    2. Keep that filter criteria.
    3. Reapply the filter w/ the last criteria.

    Does anyone know how to do that?

    Thanks!!!

  2. #2
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Keeping the filtering criteria

    See this link: Protect Sheets & Enable/Allow Autofilter
    Palmetto

    Do you know . . . ?

    You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.

  3. #3
    Registered User
    Join Date
    02-22-2007
    Posts
    47

    Re: Keeping the filtering criteria

    Hello Palmetto,

    This doesn't help. I know how to enable filters and disable in protected sheets. What I do not know is to how to keep the filter criteria that were there before I remove the autofilter, and reapply the same criteria after I put a new filter. All of my criteria are arrays...

  4. #4
    Registered User
    Join Date
    02-22-2007
    Posts
    47

    Re: Keeping the filtering criteria

    No idea? Anyone?

  5. #5
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Keeping the filtering criteria

    One approach is to create a Custom View.

    Filter the data to the state you want to return to, then go to View > Custom View > Add and give the view a meaningful name.

    After running other filters, turn off filtering, etc., to return the original state go to View > Custom View, click on the name of the view and click Show.

    Record a macro when showing the Custom View and put this in the appropriate place in your code.
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    02-22-2007
    Posts
    47

    Re: Keeping the filtering criteria

    Custom View doesn't quite work - saying "some custom view can't be shown" or something like this and doesn't put back the filter... still no solution...

  7. #7
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Keeping the filtering criteria

    Will the filter criteria you want to save always be the same?
    If so, store the values in cells and reapply the auto-filter in VBA and reference the cells holding the saved values.

    If your filter criteria is always different then possibly . . .
    • Use Advanced Filter to create a list of unique values for each column you want to filter, using the option to copy the results to a new location
    • In conjunction with the above, use the SpecialCells(xlCellTypeVisible) property to act only on the currently filtered cells
    • Create code to apply Advanced Filter (in place)
    • After running your other code, apply the Advanced Filter code, though you won't have the drop down arrows that Auto-Filter provides.

    One last option - not the most efficient
    Use Advanced Filter to create a unique list of values copied to new location
    Loop through the rows/column(s) to hide rows not matching those filtered values

  8. #8
    Registered User
    Join Date
    02-22-2007
    Posts
    47

    Re: Keeping the filtering criteria

    Hi Palmetto,

    Thanks for your hints. The solution I was thinking about (but never could get it working) is the following:

    1. Test if Autofilter is ON.
    2. Test the row on which the Autofilter is on to see if any of the columns have any Filtering keys on (the row for filters is always the same).
    3. If I find a column on which there are criterias for filtering, I take this column values and put them into another sheet and do "unique values" only in that other sheet in order to get the values on which the filtering was done.
    4. When I want to put back the filter I would like to re-use the values from the temporary sheet column to put them into an array... - and this is where I start having troubles. I do NOT know how to assign the values in the array to the filter...

    Can you, please, help?

  9. #9
    Forum Contributor
    Join Date
    08-21-2008
    Location
    Hamilton, New Zealand
    MS-Off Ver
    Office 2007
    Posts
    255

    Re: Keeping the filtering criteria

    you may find this article helpful.
    It worked a treat for me when wanting capture and restore autofilter selections
    http://www.mrexcel.com/forum/showthread.php?t=333961

  10. #10
    Registered User
    Join Date
    02-22-2007
    Posts
    47

    Re: Keeping the filtering criteria

    wotadude,

    Thanks, mate! This seems to be doing the job! I will test it and late you know.

+ Reply to 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