Clear All Filters in VBA

Clear-All-Filters-in-VBA Download File

In order to clear all filters in VBA, you have to do the following steps:

Insert data into a sheet

Click on the data and use Ctrl + T to create a table

Clear all filters in the active table

Click inside a table, and open the VBA Editor (Alt + F11).

Enter and run the following code.

Sub RemoveFiltersFromTable()
    ActiveSheet.ShowAllData
End Sub

Sub RemoveFiltersFromTable()

ActiveSheet.ShowAllData

End Sub

If an active cell is outside the table, it will return an error.

Clear all filters in the spreadsheet

In our example, there are two tables: Table1 and Table2. Each of them has applied filters.

In order to check the name of a table go to Design >> Properties >> Table Name.

You can use the previous VBA code, click on each of the tables, and run the code. But this time let’s create a code that will clear all filters from all tables in the worksheet.

In order to clear the filter inside each table on a spreadsheet, first you need to loop through all of them. Use the following code.

Sub LoopThroughTablesInsideWorksheet()
    Dim myTable     As ListObject
    For Each myTable In ActiveSheet.ListObjects
        MsgBox myTable
    Next myTable
End Sub

Sub LoopThroughTablesInsideWorksheet()

Dim myTable As ListObject

For Each myTable In ActiveSheet.ListObjects

MsgBox myTable

Next myTable

End Sub

It will return two message windows with the names of the table.

Instead of a message, let’s create code that will clear filters from each table. In order to do this, just change MsgBox myTable to myTable.AutoFilter.ShowAllData.

Sub LoopThroughTablesInsideWorksheet()
    Dim myTable     As ListObject
    For Each myTable In ActiveSheet.ListObjects
        myTable.AutoFilter.ShowAllData
    Next myTable
End Sub

Sub LoopThroughTablesInsideWorksheet()

Dim myTable As ListObject

For Each myTable In ActiveSheet.ListObjects

myTable.AutoFilter.ShowAllData

Next myTable

End Sub

The new line will check each table, and show all data, in other words, it will remove all filters.

Clear all filters in the workbook

In order to do it for all tables inside the workbook, you have to add an additional loop that will go through all the tables in each sheet and clear filters.

Sub LoopThroughTablesInsideWorkbook()
    Dim myTable     As ListObject
    Dim mySheet     As Worksheet
    
    For Each mySheet In Worksheets
        For Each myTable In mySheet.ListObjects
            myTable.AutoFilter.ShowAllData
        Next myTable
    Next mySheet
    
End Sub

Sub LoopThroughTablesInsideWorkbook()

Dim myTable As ListObject

Dim mySheet As Worksheet

For Each mySheet In Worksheets

For Each myTable In mySheet.ListObjects

myTable.AutoFilter.ShowAllData

Next myTable

Next mySheet

End Sub

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.

Post Views: 50,275