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