VBA - Clearing Filters

    Hello,


    If you using a Table, you can test following

    Code
    Sub TestClearFilters()
  Sheet1.ListObjects(1).AutoFilter.ShowAllData
End Dub

    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Love 1

    Hello,


    The number 1 is an index ...


    So, since you most probably have One Table in your sheet, there is no obligation to replace the number 1 by the Table's name :)


    Hope this clarifies

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    If you have multiple tables or only one then use


    Code
    Private Sub Worksheet_Activate()
'This macro removes any Table filtering in
'order to display all of the data but it does not remove the filter arrows
   For Each oTbl In Me.ListObjects
           oTbl.AutoFilter.ShowAllData
        Next oTbl
End Sub

    This code is worksheet event code and will run whenever the sheet is activated. For further information on how to use this code read


    Where to put your Excel VBA Code

    Quote from Jeffrey Smith

    I would like to clear all filters from table whenever the sheet is changed.


    Could you clarify ... " whenever the sheet is changed ...." ???


    Is it an event ?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Love 1
    Quote from Carim

    Could you clarify ... " whenever the sheet is changed ...." ???


    Is it an event ?

    im sorry now that i read this it isn't very clear. Not a change in the sheet. when another sheet in the workbook is selected.


    I.E.- the table that is filtered is in sheet "Parts List" and is the active sheet. Now I click on sheet "Dashboard". when this action takes place I want all the filters on sheet "Parts List" to clear.

    Quote from royUK

    If you have multiple tables or only one then use


    Code
    Private Sub Worksheet_Activate()
'This macro removes any Table filtering in
'order to display all of the data but it does not remove the filter arrows
   For Each oTbl In Me.ListObjects
           oTbl.AutoFilter.ShowAllData
        Next oTbl
End Sub

    This code is worksheet event code and will run whenever the sheet is activated. For further information on how to use this code read


    Where to put your Excel VBA Code

    thank you this is very helpful and works as you explained it. the link also is helpful.


    can I have this "oTbl.AutoFilter.ShowAllData" happen when another sheet other than the one with the table in it is activated?

    Quote from Jeffrey Smith

    im sorry now that i read this it isn't very clear. Not a change in the sheet. when another sheet in the workbook is selected.


    I.E.- the table that is filtered is in sheet "Parts List" and is the active sheet. Now I click on sheet "Dashboard". when this action takes place I want all the filters on sheet "Parts List" to clear.


    You can try following :


    Code
    Private Sub Worksheet_Activate()
' To be placed in the module of Sheet "Dashboard"
 For Each oTbl In Sheets("Parts List").ListObjects
    oTbl.AutoFilter.ShowAllData
 Next oTbl
End Sub


    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Love 1

    Thank you all:!:


    I have it working the way I want now. This is what I did.

    Code
    Private Sub Worksheet_DeActivate()

For Each otbl In Me.ListObjects

otbl.AutoFilter.ShowAllData
Next otbl

End Sub

    I just changed it to whenever the sheet is DeActivated the filter will clear. Now no matter what sheet I switch to the filter will clear.

    Thanks for your Like :thumbup:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Glad to hear you are getting the required assistance from the Forum ...!!!:):):)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Love 1

    To do this so that it will work on every sheet use the WorkBook Module


    Code
    Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'This macro removes any Table filtering in
'order to display all of the data but it does not remove the filter arrows
 For Each oTbl In Sh.ListObjects
 oTbl.AutoFilter.ShowAllData
 Next oTbl
End Sub
    Quote from royUK

    To do this so that it will work on every sheet use the WorkBook Module


    Code
    Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'This macro removes any Table filtering in
'order to display all of the data but it does not remove the filter arrows
 For Each oTbl In Sh.ListObjects
 oTbl.AutoFilter.ShowAllData
 Next oTbl
End Sub

    8|

    took it to another level there

    You should really declare oTbl as a ListObject. My example workbook that I took the code snippet from uses oTbl in several procedures and so it is declared separately


    Code
    Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim oTbl As ListObject
'This macro removes any Table filtering in
'order to display all of the data but it does not remove the filter arrows
 For Each oTbl In Sh.ListObjects
 oTbl.AutoFilter.ShowAllData
 Next oTbl
End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login