Clear all filters in VBA

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

  1. Insert data into a sheet

  1. 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

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 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

It will return two message windows with 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

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

Clear all filter 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

Posted in vba