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.

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.

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.

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.

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