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