none
Is there a quick, easy way to AutoFilter.Clear ? RRS feed

  • Question

  • I tried searching on the web and what not, and everywhere I look people are setting up

    If autofiltermode=true then autofiltermode= false, etc, etc, etc.. statements

    Isn't there just a ListObject..., or Range.AutoFilter.Clear? like there is for Sort.SortFields.Clear.. Wait, is it AutoFilter.AutoFilterFields.Clear ? Let me try that.. brb..

    EDIT: Bah, didn't work.. ok so back to my question.. what's the proper way to clear all the settings of the AutoFilter without having to go loop, if then etc.. or isn't there one?

    Thanks!

    EDIT: And Just for the sake of clarity, I just want to ensure that the table has all filters, sorts, etc removed and is in it's basic format before proceeding to my next step in the code. Thanks again !

     


    I'm new, please help. Begging for forgiveness in advance.

     


    • Edited by JMStumpf Thursday, November 10, 2011 8:33 PM
    Thursday, November 10, 2011 8:23 PM

Answers

  • Because you are talking ListObjects then I am assuming that you require the following.

    If ActiveSheet.Range("Table1").ListObject.ShowAutoFilter Then
      ActiveSheet.ListObjects("Table1").AutoFilter.ShowAllData
    End If

    Without testing if ShowAutoFilter is applied to the table the ShowAllData will error if AutoFilter is not applied to the table.

    Added with edit:

    The following is the method of testing if a filter is actually set although there is no point in doing so if you just want to ShowAllData. Again, without testing if ShowAutoFilter is applied to the table the FilterMode will error if AutoFilter is not applied to the table.

    If ActiveSheet.Range("Table1").ListObject.ShowAutoFilter Then
      MsgBox "AutoFilter are turned on"
      If ActiveSheet.ListObjects("Table1").AutoFilter.FilterMode Then
        MsgBox "Filter is actually set"
        ActiveSheet.ListObjects("Table1").AutoFilter.ShowAllData
      End If
    End If

    The previous methods retain the AutoFilter on the table; just sets all Filters to ShowAll.

    The following also works but it turns AutoFilter off (removes dropdown arrows). Do not need to test first because the code does not error if AutoFilter is not turned on.

    ActiveSheet.Range("Table1").ListObject.ShowAutoFilter = False

    Cannot use similar code with FilterMode because FilterMode is read only and cannot be set with code.

    Note: All of this answer refers to AutoFilter on Tables (ListObjects). Similar code used where AutoFilter is applied to a worksheet without tables but with slightly different syntax.


    Regards, OssieMac

    Friday, November 11, 2011 7:56 AM
  • EDIT: Funny enough simply using the following solved my issue: ActiveSheet.ListObjects(1).AutoFilter.ShowAllData

    Sometimes the simplest answer is also the hardest to find.

    Sorry to have to tell you this but sometimes the simplest method will cause you more problems down the track. If a user turns off the AutoFiltering on the table and then runs your code then the code will error because you cannot use ShowAllData if ShowAutoFilter is turned off.

    In my previous post I explained that this is the reason for testing if ShowAutoFilter is true before using ShowAllData. The correct coding if using the ListObject item rather than the table name is as follows so that no error will occur if ShowAutoFilter is turned off.

    If ActiveSheet.ListObjects(1).ShowAutoFilter Then
      ActiveSheet.ListObjects(1).AutoFilter.ShowAllData
    End If

    When writing code you should try to allow for all possible eventualities and handle them in such a way that the end user will not be inconvenienced with errors.


    Regards, OssieMac
    Wednesday, November 16, 2011 6:11 AM

All replies

  • hi,
     
    With ActiveSheet  'you must adapt the object names "sheet and range"
    If .AutoFilterMode = True Then .Range("A1").AutoFilter: .Range("A1").AutoFilter
    End With
     --
    isabelle
     
    Le 2011-11-10 15:23, JMStumpf a écrit :
    > I tried searching on the web and what not, and everywhere I look people are setting up
    >
    > If autofiltermode=true then autofiltermode= false, etc, etc, etc.. statements
    >
    > Isn't there just a ListObject..., or Range.AutoFilter.Clear? like there is for Sort.SortFields.Clear.. Wait, is it AutoFilter.AutoFilterFields.Clear ? Let me try that.. brb..
    >
    > EDIT: Bah, didn't work.. ok so back to my question.. what's the proper way to clear all the settings of the AutoFilter without having to go loop, if then etc.. or isn't there one?
    >
    > Thanks!
    >
    > EDIT: And Just for the sake of clarity, I just want to ensure that the table has all filters, sorts, etc removed and is in it's basic format before proceeding to my next step in the code. Thanks again !
    >
    > --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    > I'm new, please help. Begging for forgiveness in advance.
    >
    >
     
    Thursday, November 10, 2011 11:49 PM
  • Because you are talking ListObjects then I am assuming that you require the following.

    If ActiveSheet.Range("Table1").ListObject.ShowAutoFilter Then
      ActiveSheet.ListObjects("Table1").AutoFilter.ShowAllData
    End If

    Without testing if ShowAutoFilter is applied to the table the ShowAllData will error if AutoFilter is not applied to the table.

    Added with edit:

    The following is the method of testing if a filter is actually set although there is no point in doing so if you just want to ShowAllData. Again, without testing if ShowAutoFilter is applied to the table the FilterMode will error if AutoFilter is not applied to the table.

    If ActiveSheet.Range("Table1").ListObject.ShowAutoFilter Then
      MsgBox "AutoFilter are turned on"
      If ActiveSheet.ListObjects("Table1").AutoFilter.FilterMode Then
        MsgBox "Filter is actually set"
        ActiveSheet.ListObjects("Table1").AutoFilter.ShowAllData
      End If
    End If

    The previous methods retain the AutoFilter on the table; just sets all Filters to ShowAll.

    The following also works but it turns AutoFilter off (removes dropdown arrows). Do not need to test first because the code does not error if AutoFilter is not turned on.

    ActiveSheet.Range("Table1").ListObject.ShowAutoFilter = False

    Cannot use similar code with FilterMode because FilterMode is read only and cannot be set with code.

    Note: All of this answer refers to AutoFilter on Tables (ListObjects). Similar code used where AutoFilter is applied to a worksheet without tables but with slightly different syntax.


    Regards, OssieMac

    Friday, November 11, 2011 7:56 AM
  • Thank you both for the replies I can see where both suggestions would be helpful in different situations. My current situation is based off a Table and I have to sort different columns at different times throughout the macro. I was looking for an easy way to ensure any previous autofilter settings and criterias were cleared and all possible data was showing, prior to moving to the next steps of the macro. I think I will try OssieMac's suggestion this morning. Thanks again you all are life savers!

    EDIT: Funny enough simply using the following solved my issue: ActiveSheet.ListObjects(1).AutoFilter.ShowAllData

    Sometimes the simplest answer is also the hardest to find.


    I'm new, please help. Begging for forgiveness in advance.

    • Edited by JMStumpf Tuesday, November 15, 2011 2:30 PM
    Tuesday, November 15, 2011 2:21 PM
  • EDIT: Funny enough simply using the following solved my issue: ActiveSheet.ListObjects(1).AutoFilter.ShowAllData

    Sometimes the simplest answer is also the hardest to find.

    Sorry to have to tell you this but sometimes the simplest method will cause you more problems down the track. If a user turns off the AutoFiltering on the table and then runs your code then the code will error because you cannot use ShowAllData if ShowAutoFilter is turned off.

    In my previous post I explained that this is the reason for testing if ShowAutoFilter is true before using ShowAllData. The correct coding if using the ListObject item rather than the table name is as follows so that no error will occur if ShowAutoFilter is turned off.

    If ActiveSheet.ListObjects(1).ShowAutoFilter Then
      ActiveSheet.ListObjects(1).AutoFilter.ShowAllData
    End If

    When writing code you should try to allow for all possible eventualities and handle them in such a way that the end user will not be inconvenienced with errors.


    Regards, OssieMac
    Wednesday, November 16, 2011 6:11 AM
  • Awesome!

    Thanks

    Friday, July 17, 2015 8:35 PM