Consulting

Results 1 to 11 of 11

Thread: Solved: Auto - Filter (check on/off Status)

  1. #1
    VBAX Mentor asingh's Avatar
    Joined
    Jul 2005
    Posts
    307
    Location

    Solved: Auto - Filter (check on/off Status)

    The function "Selection.Autofilter" can be used to add/remove an auto filter to a selected row. If a row has auto filters all ready on it, then using "Selection.Autofilter" removes these auto - filters. If a row does not have auto - filters then using "Selection.Autofilter" adds auto - filters to that row. Is then any way that I can run a "check" that will tell me if that row has auto-filters all ready set on it..?

    Thanks a lot for the help.

    asingh

  2. #2
    VBAX Regular HaHoBe's Avatar
    Joined
    Aug 2004
    Location
    Hamburg
    Posts
    89
    Location
    Hi,

    make good use of AutoFilterMode:

    [vba]Option Explicit

    Sub asingh()
    If ActiveSheet.AutoFilterMode Then
    MsgBox "AutoFilter: on"
    Else
    MsgBox "AutoFilter: off"
    End If
    End Sub[/vba]
    Ciao,
    Holger

  3. #3
    VBAX Master XLGibbs's Avatar
    Joined
    Jan 2006
    Location
    state of confusion, but vacation in denial
    Posts
    1,315
    Location
    Or you can also use

    Selection.AutoFilter to turn it on and

    ActiveSheet.ShowAllData to turn it off
    If you have posted the same question at multiple forums, please read this IMPORTANT INFO.

    Please use the thread tools to mark your thread Solved


    Please review the Knowledge Base
    for samples and solutions , or to submit your own!




  4. #4
    VBAX Mentor asingh's Avatar
    Joined
    Jul 2005
    Posts
    307
    Location
    Hi,

    The option "If Activesheet.Autofiltermode ", seems to work perfect. I am able to trap if the auto filters are on or off.

    When I tried the other method as suggested by Ha ha Ho Be, "Activesheet.Showalldata", I get the following run - time error "ShowAllData method of worksheet class failed". I made sure the filters were on when I tried this.

    My purpose is solved using the IF condition, but I was still wondering if the ShowAllData method could be made to work, it does seem good to use..

    thanks


    asingh

  5. #5
    VBAX Mentor asingh's Avatar
    Joined
    Jul 2005
    Posts
    307
    Location
    Sorry about that...XLGibbs had suggested "ActiveSheet.ShowAllData" method....!

  6. #6
    Moderator VBAX Master geekgirlau's Avatar
    Joined
    Aug 2004
    Location
    Melbourne, Australia
    Posts
    1,464
    Location
    Generally speaking I find that the purpose of detecting whether the autofilter is on or not is because you want to apply a filter with only your criteria. I use something like the following:

    [vba]
    If ActiveSheet.AutoFilterMode Then
    ActiveSheet.ShowAllData
    End If
    [/vba]

    Or ...
    [vba]
    On Error Resume Next
    ActiveSheet.ShowAllData
    [/vba]

  7. #7
    Administrator
    2nd VP-Knowledge Base
    VBAX Master malik641's Avatar
    Joined
    Jul 2005
    Location
    Florida baby!
    Posts
    1,533
    Location
    Quote Originally Posted by asingh
    Is then any way that I can run a "check" that will tell me if that row has auto-filters all ready set on it..?

    asingh
    I'm still curious if what he originally asked for is even possible....




    New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.

  8. #8
    VBAX Mentor asingh's Avatar
    Joined
    Jul 2005
    Posts
    307
    Location
    I get an "ShowAllData method of worksheet class failed" error when trying to use the method "ActiveSheet.ShowAllData", even if the sheet has auto - filters set on it....?

  9. #9
    VBAX Mentor asingh's Avatar
    Joined
    Jul 2005
    Posts
    307
    Location
    Okay...both the methods are now working....to get "ActiveSheet.ShowAllData" to work, Auto - filters have to be on, and a filter critereon has to be set. I was not doing that. The auto - filters were on but the critereon was not defined.

    thanks to all......

    regards,

    ASingh

  10. #10
    VBAX Newbie
    Joined
    Jun 2007
    Posts
    1
    Location

    checking if autofilter criteria is in force

    So how do you establish if an auto filter criteria is in place ?

    What i would like to do is to show all data if a filter is in place. I know i can achieve that if i switch off all filtering and recreate the filtering but I want to keep the existing auto filter. Also I don;t like doing it by allowing ShowAllData to error and ignoring the error - i just want to make sure that all rows are visible and there are no filtered out rows. Doing Activesheet.showalldata fails if a filter criteria is not selected.

    Regards

  11. #11
    Moderator VBAX Master geekgirlau's Avatar
    Joined
    Aug 2004
    Location
    Melbourne, Australia
    Posts
    1,464
    Location
    Quote Originally Posted by sajina
    Doing Activesheet.showalldata fails if a filter criteria is not selected.
    ... which is why you have the "On Error Resume Next"
    • If the data is filtered and criteria is selected, the criteria is removed so all rows are displayed.
    • If there is no autofilter on, all data is already displayed.
    • If the filter is on but no criteria is selected, all data is already displayed.
    So to restate, the simplest solution is

    [VBA]
    On Error Resume Next
    ActiveSheet.ShowAllData
    On Error GoTo ErrHandler
    [/VBA]

Posting Permissions

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