To remove filters from data, we are going to use the following dataset. It contains some product IDs in column B, product names in column C, and the delivery country in column D. As we want to see only the details of the product Shampoo and Conditioner, we filtered them. Let’s clear those filters.
Method 1 – Apply VBA to Remove All Filters from an Excel Table
Steps:
- Go to the Developer tab from the ribbon.
- From the Code category, click on Visual Basic to open the Visual Basic Editor. You can also press Alt + F11 to open the Visual Basic Editor.
- You can also right-click on your worksheet and go to View Code.
- Click on Module from the Insert drop-down menu bar. This will create a Module in your workbook.
- Copy and paste the VBA code shown below:
VBA Code:
Sub Remove_Filters1()
Dim lstObj As ListObject
Set lstObj = Sheet1.ListObjects(1)
lstObj.AutoFilter.ShowAllData
End Sub- Run the code by clicking on the RubSub button or pressing the keyboard shortcut F5.
- This will remove all the filters from the Excel table on your worksheet.
VBA Code Explanation
Sub Remove_Filters1()Sub is a part of code that is used to handle the work in the code but will not return any value. It is also known as subprocedure. So we name our procedure Remove_Filters1().
Dim lstObj As ListObjectVariable declaration.
Set lstObj = Sheet1.ListObjects(1)VBA Set simply allows us to avoid having to type in the range we need to pick over and over again when running the code. So, we set the reference to the first table on the sheet.
lstObj.AutoFilter.ShowAllDataThis line of code will remove all filters for the entire data.
End SubThis will end the procedure.
Read More: Filter Multiple Criteria in Excel with VBA (Both AND and OR Types)
Method 2 – Clear All Excel Table Filters on a Sheet Using VBA
Steps:
- Press Alt + F11 to open the VBA window or right-click on the sheet and select View Code.
- Go to Insert and select Module from the drop-down menu to insert a new Module.
- Copy and paste the VBA code below into the module:
VBA Code:
Sub Remove_Filters2()
Dim lstObj As ListObject
For Each lstObj In Sheet2.ListObjects
lstObj.AutoFilter.ShowAllData
Next lstObj
End Sub- Press the F5 key or click on the Run Sub button to run the code.
- This code will clear all Excel table filters from your sheet and give the same output as Method 1.
VBA Code Explanation
For Each lstObj In Sheet2.ListObjects
lstObj.AutoFilter.ShowAllData
Next lstObjThose lines of code loop through all tables on the sheet and remove all filters for the entire worksheet.
Read More: How to Filter Based on Cell Value Using Excel VBA
Method 3 – Remove a Filter from a Column with VBA in Excel
Steps:
- Press Alt + F11 to open the VBA window or right-click on the sheet and select View Code.
- Go to Insert and select Module from the drop-down menu to insert a new Module.
- Copy and paste the VBA code below into the module:
VBA Code:
Sub Remove_Filter3()
Sheet1.Range("B3:D16").AutoFilter Field:=4
End Sub- Press the F5 key to run the code.
- Using this code will remove the filter from the entire range in our Excel table.
VBA Code Explanation
Sheet1.Range("B3:D16").AutoFilter Field:=4This code line specifies the field number only and no other parameters.
Read More: Excel VBA: How to Filter with Multiple Criteria in Array
Method 4 – Clear All Filters in an Active Worksheet
Steps:
- Press Alt + F11 to open the VBA window or right-click on the sheet and select View Code.
- Go to Insert and select Module from the drop-down menu to insert a new Module.
- Copy and paste the VBA code below into the module:
VBA Code:
Public Sub RemoveFilter()
If ActiveSheet.FilterMode = True Then
ActiveSheet.ShowAllData
End If
End Sub- Run the code by pressing the F5 key.
- This removes all filters from the active worksheet and yields the same result as Method 1.
Read More: Excel VBA to Filter in Same Column by Multiple Criteria
Method 5 – Excel VBA to Remove All Filters from a Workbook
STEPS:
- Press Alt + F11 to open the VBA window or right-click on the sheet and select View Code.
- Go to Insert and select Module from the drop-down menu to insert a new Module.
- Copy and paste the VBA code below into the module:
VBA Code:
Sub Remove_Filters_From_Workbook()
Dim shtnam As Worksheet
Dim lstObj As ListObject
For Each shtnam In Worksheets
For Each lstObj In shtnam.ListObjects
lstObj.AutoFilter.ShowAllData
Next lstObj
Next shtnam
End Sub- Run the code by pressing F5 on your keyboard.
VBA Code Explanation
For Each shtnam In Worksheets
For Each lstObj In shtnam.ListObjects
lstObj.AutoFilter.ShowAllData
Next lstObj
Next shtnamThe first loop is for looping through all tables in the workbook. The second loop is for looping through all tables on the worksheet. Then, the line inside the loop just clears the filter from the table. After that, close the loop with the last two lines.
Download the Practice Workbook
You can download the workbook and practice with it.
Related Articles
- Excel VBA: Filter Based on Cell Value on Another Sheet
- Excel VBA: Filter Table Based on Cell Value
- VBA Code to Filter Data in Excel
- Filter Different Column by Multiple Criteria in Excel VBA
