Results 1 to 11 of 11
  1. #1
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Detecting an Autofilter on a worksheet.

    Hi Lounger

    Is there a way to detect if a worksheet is filtered?

    If that is a possibility, how can I unfilter it, via VBA?

    Thanks

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Wassim,

    This should do the trick:
    Code:
       With ActiveSheet
    
            If .AutoFilterMode Then   'it's on turn if off
              .AutoFilterMode = False
    Or for multiple sheets:
    Code:
     
          For Each oSheet In ActiveWorkbook.Sheets
               If oSheet.AutoFilterMode Then _
                 oSheet.AutoFilterMode = False
          Next oSheet
    Actually, I'm not sure the previous will work as I edited it on the fly but I know this will work:
    Code:
           
          For Each oSheet In ActiveWorkbook.Sheets
               oSheet.Select
               If ActiveSheet.AutoFilterMode Then _
                 ActiveSheet.AutoFilterMode = False
            Next oSheet
    Last edited by RetiredGeek; 2011-10-14 at 21:04.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    RetiredGeek,
    There is no need to select the sheets. So your 2nd code is better than the 3rd. There is a problem with both codes if the workbook has chart sheets in them. Your code will also try to look for autofiltering in the chartsheet which will give an error. The solution is to only loop through the worksheets in the workbook, not all the sheets. Also it is always good practice to declare the variables:

    Code:
    Dim oSheet As Worksheet
    For Each oSheet In ActiveWorkbook.Worksheets
      If oSheet.AutoFilterMode Then _
        oSheet.AutoFilterMode = False
    Next oSheet
    Steve

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by sdckapr View Post
    RetiredGeek,
    There is no need to select the sheets. So your 2nd code is better than the 3rd. There is a problem with both codes if the workbook has chart sheets in them. Your code will also try to look for autofiltering in the chartsheet which will give an error. The solution is to only loop through the worksheets in the workbook, not all the sheets. Also it is always good practice to declare the variables:
    Steve
    Steve,

    Thanks, I didn't know about the chart sheet problem. I always do declare my variables but I cut this piece of code from a larger procedure and I should have also cut the declarations.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    What did you declare oSheet as?

    When you use ActiveWorkbook.Sheets it must be a variant to account for non-worksheets and will give an runtime error if declared as a worksheet (when non-worksheets are in the workbook) [You also would can get a runtime error if the chart sheet does not support any worksheet property, like autofilter]. When you use ActiveWorkbook.Worksheets you can declare it as a worksheet...

    Steve

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Steve,

    I just had it declared as a generic object: Dim oSheet As Object. This is from some old code, I know better now!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    An object will work for looping through the sheets and will not cause an error for having it mis-assigned. It still would give an error if you tried to use a property it does not have (and a chart sheet has a lot less properties than a worksheet!)

    [I hope you don't take this advice the wrong way. Unfortunately I learned the hard way about these issue when it happened in a workbook and it took me a while to understand the mistake and I was just trying to educate you (and anyone else following the thread) about the subtle, but important, difference between activeworkbook.sheets and activeworkbook.worksheets]

    Steve

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by sdckapr View Post

    [I hope you don't take this advice the wrong way.]

    Steve
    Steve,

    Won't ever happen. I realized long ago, too long, that nobody knows it all...not even Bill Gates! I always appreciate when someone who knows something I don't shares it. Too often people, not those in the lounge of course, who have information hoard it as a way to gain power, status, job security, or what ever. So keep right on sharing your hard earned knowledge. I for one will always appreciate it even if it makes me look a little silly in the process. A small price to pay for new knowledge.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #9
    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
    Just as an FYI, there is no need for the If test when removing a filter:
    Code:
    Dim oSheet As WorksheetFor Each oSheet In ActiveWorkbook.Worksheets
        oSheet.AutoFilterMode = False
    Next oSheet
    on the other hand if Wassim wants the filter left in place, but all data shown, then you can use:
    Code:
       Dim oSheet            As Worksheet
       For Each oSheet In ActiveWorkbook.Worksheets
          If oSheet.AutoFilterMode Then
             If oSheet.FilterMode Then oSheet.ShowAllData
          End If
       Next oSheet
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by rory View Post
    Just as an FYI, there is no need for the If test when removing a filter:
    Rory,

    As I remember, and I have CRS really bad, it used to throw an error if you tried to set it to False and there was no filter set. I just tested it w/2003 and it works as you stated and I no longer have any older versions to test. As I stated this is really OLD, like me, code.
    Thanks for the new, to me, info.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  11. #11
    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
    To the best of my recall, turning it off always works. Most other filter work requires the test, but not that. (I do have a copy of 2000 lying around somewhere, so I'll test if I get a chance)
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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