Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Testing for Data Filters in a macro

    I'd like to clear or remove any Data Filters from a worksheet as one of the first steps in a macro.

    When Filters exist:
    Selection.AutoFilter will remove existing sheet's filter(s) or
    ActiveSheet.ShowAllData will clear any/all filter selections while leaving the AutoFilters intact

    But my worksheet may not always have Filters enabled. So including either of these statements in the code when there are no filters (or no filter selections) causes the macro to bomb.

    Is there a way to test the sheet for Filters and turn them off (remove) if they exist?
    How can the code then be modified to simply Clear the sheet's Filters?

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    This works for me in Excel 2003:

    Code:
       With ActiveSheet
    
           If .AutoFilterMode Then   'it's on turn if off
             .AutoFilterMode = False
           Else
           End If
           
       End With   'Activesheet
    Here's a way to do it for all sheets in a workbook.
    Code:
           For Each oSheet In ActiveWorkbook.Sheets
              oSheet.Select
              If ActiveSheet.AutoFilterMode Then _
                ActiveSheet.AutoFilterMode = False
           Next oSheet
    In the code directly above you can probably kill the oSheet.Select line and substitute oSheet for ActiveSheet in the following 2 lines. Don't forget to Dim oSheet as Workseet.
    Last edited by RetiredGeek; 2011-07-05 at 17:16.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Thanks RG. I tried the first version and it turns off Filtering for me too. (XL2007)

    I might try your second solution, but I'm not clear on oSheet. Please tell me more; how's that work?

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    oSheet is just a variable name. The variable is declared {Dim} as type Worksheet. Then the For Each oSheet in ActiveWorkbook.Worksheets runs through the loop code for each Worksheet in the Workbook assigning the current Worksheet object to the variable oSheet. You then use oSheet as the object to do your work on. Thus, you can turn off the filters in any/all the sheets in a given workbook with one block of code and one call to it.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    If you want to turn off filters regardless, there is no need for the If test - just:
    Code:
    ActiveSheet.AutoFilterMode = False

    will be fine.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Thanks RG and Rory.

    As long as i'm in the neighborhood, What would be the code (if any) to Clear the Filter rather then turn it off?

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You need the Showalldata method of the worksheet.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. The Following User Says Thank You to rory For This Useful Post:

    RetiredGeek (2011-07-06)

Posting Permissions

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