Results 1 to 8 of 8

Thread: Saving filters

  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,766
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Saving filters

    Hi All,

    I have 2 questions about filtering applying to both Excel 2003 and for 2010 which I'm still transitioning to.

    1. Is there a way to have >2 conditions in a Custom Filter. Certainly for text-based columns, this could come in handy to filter rows equal to 1 of 3 or 4 items. I guess I could go back and create an extra column using an IF that would create one value (eg, something like "good stuff") if the column was one of my desired items but that seems like a kludge. I thought since Conditional Formatting got a facelift, maybe Custom Filter did too and I just didn't get the memo.

    2. Is there a way to save the Auto Filter conditions, especially if they're Custom filters? Sometimes I filter on several columns but then decide to look at all the rows again. If I then want to go back to the filtered data, I have to reset all the filters. I believe I could accomplish this with Advanced Filters but I usually have to play around with that since I use this feature so infrequently. What I'd ideally like with this is, for example, a way of doing 4 things:
    - save filter info (assumes some filtering is in effect);
    - restore all rows w/ no filtering but w/o the filter info lost;
    - restore all filters;
    - reset all filters to show "All" - I guess that would basically be the same as unchecking the box in the Data | Filter menu or the Ribbon equivalent which wipes out all the filter info (I could probably record that macro)

    I wonder if restoring filters could be accomplished by saving the filtered condition as a View but haven't tried it - thought just occurred to me.

    TIA

    Fred

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,224
    Thanks
    14
    Thanked 341 Times in 334 Posts
    1) AFAIK only 2 filters are allowed in custom filters. If I need multiple ones, you can use as you noted an intermediate column which tests the criteria. In 2010 you do have the option of selecting multiple items from the list which can sometimes be more useful than multiple criteria

    2) Use a custom view:
    Set filter criteris
    View - custom views - Add
    Make sure, hidden rows filters... is checked
    Give it a name[ok]
    You can create multiple views with various filter criteria

    Steve

  4. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,766
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Hi Steve,

    Thanks much for the reply. I applied the thoughts to Excel 2003, nothing tested in 2010 yet.

    I created a custom view with the cols filtered as I want. Then I recorded what turned out to be 2 one-line macros that do what I want - almost.
    - to turn on the filter: ActiveWorkbook.CustomViews("filter_for_SqF_YrB_HsT ").Show
    - to turn off the filter: Selection.AutoFilter

    I also created a button on my toolbar for each and assigned one macro to each button. No rocket science here.

    The sheet to be filtered has a freeze in A2 to prohibit the heading row from scrolling. Again no rocket science (I like the 2010 feature that just allows you to select "first row" or "first column" so you don't even have to position the mouse in the desired cell - always found people had problem understanding where to position the mouse for a desired freeze effect).


    When I turned on and then off my filters via the macros, the freeze "jumped." That is, it was no longer under Row 1. It was now under Row 39. Any significance to Row 39? Yep - the first filtered row to appear was Row 40. Coincidence - I think not. Annoying - you bet!

    Any thoughts?

    BTW: For turning off filter off, I would have thought there was some way to "unview" the Custom View - that is, view things as if there was no Custom View in effect. I tried to see what it would be in VBA by "retyping" the 1-line in the "turn on" macro starting with the period but nothing in the menu of the "CustomViews" object seemed to be appropriate. Hence, I recorded the macro for turning off as per the above.

    TIA

    Fred

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,171
    Thanks
    200
    Thanked 781 Times in 715 Posts
    Fred,

    To fix the first problem put: [A2].Select as the first line of the macro. Since turning on Freeze uses the current cursor position you want to make sure it's where you want. Sorry there is no way to turn off a Custom View once set w/o deleting it. What you can do is to create a custom view of the standard settings and call it Normal then create the custom view you want now you can use the same statement to switch back to Normal.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  6. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,894
    Thanks
    0
    Thanked 84 Times in 80 Posts
    In 2007+ you can filter for arrays of items, as long as it is a straight 'equal to' condition.

    To save your filters, you would have to loop through the filters, check if each is On, then store its operator, and both criteria (if applicable) in a collection.
    Regards,
    Rory
    Microsoft MVP - Excel.

  7. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,766
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Got it!

    Hi Retired Geek,

    Your idea of first selecting A2 didn't work - the freeze still came out starting with Row 40 (I already had a freeze for the first row so as not to scroll the col headings). I also have col A hidden so I changed your suggestion to [B2].Select and even tried Range("B2").Select (see below) as the first statement but that had the same result. It seems like the Selection.AutoFilter has some weird result in terms of a freeze.

    But it gave me an idea. So my turning off filters macro is now:
    Selection.AutoFilter
    ActiveWindow.FreezePanes = False
    Range("B2").Select
    ActiveWindow.FreezePanes = True

    If I run into problems with this (I've tested it and it seems ok), I can go to your suggestion of having a Custom View that just looks at the entire sheet.



    Rory
    I have 4 filters: one is a not equal while the others are combinations of > and <

    I'm assuming your 2nd comment applies to 2003. Seems like creating the custom view is easier. Also, supposing I had several sheets with filters (seems like AutoFilter can be on/off on a sheet basis). How does one check if a filter is on for the sheet in question? Would this be something in a sheet module? Just curious since you raised the possibility.


    Thanks guys.

    Fred

  8. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,894
    Thanks
    0
    Thanked 84 Times in 80 Posts
    I'd use a Collection or Dictionary to store the autofilter info, with one per sheet. If you use the sheet name as the key, it should be easy enough to retrieve the data for each sheet as required.
    Regards,
    Rory
    Microsoft MVP - Excel.

  9. #8
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,766
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Hi Rory,

    Thanks. Since I've never used collections or dictionaries in VBA, I'll pass on this for now. But it does seem to be along the lines of my original request. Something to keep in mind for the future.

    Fred

Posting Permissions

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