Method 1 – Remove AutoFilter from Active Worksheet If It Exists
❶ Press ALT + F11 to open the VBA Editor.
❷Go to Insert >> Module.
❸ Copy the following VBA code.
Public Sub RemoveAFActiveWorksheet()
If ActiveSheet.AutoFilterMode Then
ActiveSheet.AutoFilterMode = False
End If
End Sub❹ Paste and Save the code in the VBA Editor.
Breakdown of the Code
- We created a Sub procedure Public Sub RemoveAFActiveWorksheet
- We used an IF statement to check if there exists any AutoFilter if exists it will remove the AutoFilter as we set the AutoFilterMode to False.
❺ Go back to the active worksheet and press ALT + F11 to open the Macro dialog box.
❻ Select the macro named RemoveAFActiveWorksheet and hit the Run button.
See the AutoFilter has been removed and all the data are visible now.
Method 2 – Using VBA to Delete AutoFilter from All Worksheets
❶ Press ALT + F11 to open the VBA Editor.
❷Go to Insert >> Module.
❸ Copy the following VBA code.
Public Sub DeleteAFfromallWorksheets()
Dim xWs1 As Worksheet
For Each xWs1 In ActiveWorkbook.Worksheets
If xWs1.AutoFilterMode = True Then
xWs1.AutoFilterMode = False
End If
Next xWs1
End Sub❹ Paste and Save the code in the VBA Editor.
Breakdown of the Code
- We used a For loop to search for the AutoFilter in each worksheet.
- We used an IF statement to check if there exists an If exists, it will remove the AutoFilter as I set the ActiveSheet.AutoFilterMode to False.
❺ Go back to your worksheet and press ALT + F11 to open the Macro dialog box.
❻ Select the macro RemoveAFfromallWorksheets and hit the Run button.
See all the AutoFilter icons are removed from all the worksheets in your workbook, like the picture below:
Method 3 – Clear AutoFilter from a Single Column of a Table
❶ Press ALT + F11 to open the VBA Editor.
❷Go to Insert >> Module.
❸ Now Copy the following VBA code.
Sub DeleteAFSingleColumnfromTable()
Dim xWs1 As Worksheet
Dim xTableName1 As String
Dim xLT1 As ListObject
xTableName1 = "TableA"
Set xWs1 = Sheets("MyTable1")
Set xLT1 = xWs1.ListObjects(xTableName1)
xLT1.Range.AutoFilter Field:=1
End Sub❹ Paste and Save the code in the VBA Editor.
Breakdown of the Code
- We declared 3 variables.
- We inserted the table name and Set the sheet name.
- We used the ListObjects property to make visible all the contents of a table.
- We input a table column index using the AutoFilter Field
❺ Go back to your worksheet and press ALT + F11 to open the Macro dialog box.
❻ Select the macro RemoveAFSingleColumnfromTable and hit the Run button.
See the AutoFilter exists no more in the first column of your table.
Method 4 – Remove AutoFilter from Multiple Columns of a Table
❶ Press ALT + F11 to open the VBA Editor.
❷ Go to Insert >> Module.
❸ Copy the following VBA code.
Sub DeleteAFMultiColumnsfromTable()
Dim xWs1 As Worksheet
Dim xTableName1 As String
Dim xLT1 As ListObject
xTableName1 = "TableA"
Set xWs1 = Sheets("MyTable1")
Set xLT1 = xWs1.ListObjects(xTableName1)
xLT1.Range.AutoFilter Field:=1
xLT1.Range.AutoFilter Field:=2
End Sub❹ Paste and Save the code in the VBA Editor.
Breakdown of the Code
- We declared 3 variables.
- We inserted the table name and Set the sheet name.
- We used the ListObjects property to make visible all the contents of a table.
- We input two table column indexes using the AutoFilter Field
❺ Go back to the worksheet and press ALT + F11 to open the Macro dialog box.
❻ The macro DeleteAFMultiColumnsfromTable and hit the Run button.
After running the code, the AutoFilter is removed from multiple columns.
Method 5 – Clear AutoFilter from an Entire Table Using Excel VBA
❶ Press ALT + F11 to open the VBA Editor.
❷ Go to Insert >> Module.
❸ Copy the following VBA code.
Sub RemoveAFfromEntireTable()
Dim xWs1 As Worksheet
Dim xTable1 As String
Dim xTable2 As ListObject
xTable1 = "TableB"
Set xWs1 = ActiveSheet
Set xTable2 = xWs1.ListObjects(xTable1)
xTable2.AutoFilter.ShowAllData
End Sub❹ Paste and Save the code in the VBA Editor.
Breakdown of the Code
- We declared 3 variables.
- We inserted the table name next Set the sheet name as ActiveSheet.
- We used the ListObjects property to make visible all the contents of a table and used the Set statement to store it in xTable2.
- We used the ShowAllData property to turn off the AutoFilter.
❺ Go back to the worksheet having a table and press ALT + F11 to open the Macro dialog box.
❻ Select the macro RemoveAFfromEntireTable and hit the Run button.
The AutoFilter will be removed from the entire table, just like in the picture below:
Method 6 – Delete AutoFilter from Password Protected Worksheet If Exists
❶ Press ALT + F11 to open the VBA Editor.
❷ Go to Insert >> Module.
❸ Copy the following VBA code.
Sub RemoveAFwithPass()
Dim UserPwd As String
UserPwd = "7878"
With ActiveSheet
.Unprotect Password:=UserPwd
.ShowAllData
.Protect _
Contents:=True, _
AllowFiltering:=True, _
UserInterfaceOnly:=True, _
Password:=UserPwd
End With
End Sub❹ Paste and Save the code in the VBA Editor.
Breakdown of the Code
- We declared a variable and input the password.
- In the ActiveSheet, I assigned the User Password to the Unprotect Password.
- Used ShowAllData property to unhide everything.
- We assigned True to Contents, AllowFiltering, and UserInterfaceOnly to unprotect them all.
❺ Go back to the password-protected worksheet and press ALT + F11 to open the Macro dialog box.
❻ Select the macro RemoveAFwithPass and hit the Run button.
The AutoFilter will be removed, and all the records will be visible like this:
Method 7 – Use of VBA to Remove AutoFilter from Protected Worksheet without Password
❶Press ALT + F11 to open the VBA Editor.
❷ Go to Insert >> Module.
❸ Copy the following VBA code.
Sub RemoveAFwithoutPass()
With ActiveSheet
.Unprotect
.ShowAllData
.Protect _
Contents:=True, _
AllowFiltering:=True, _
UserInterfaceOnly:=True
End With
End Sub❹ Paste and Save the code in the VBA Editor.
Breakdown of the Code
- We applied the With statement in the ActiveSheet and used the Unprotect property to unlock.
- We used the ShowAllData property to unhide everything.
- We assigned True to Contents, AllowFiltering, and UserInterfaceOnly to unprotect them all.
❺ Go back to the active worksheet and press ALT + F11 to open the Macro dialog box.
❻ Select the macro RemoveAFwithoutPass and hit the Run button.
The AutoFilter will be cleared out, and all the data will be visible like this:
Download Practice Workbook
You can download the Excel file from the following link and practice along with it.
Related Articles
- How to Autofilter Values Not Equal to a Certain Value with VBA in Excel
- VBA Autofilter: Sort Smallest to Largest
- VBA to AutoFilter with Multiple Criteria on Same Field in Excel
