Microsoft Excel offers multiple ways to remove an AutoFilter from a worksheet or an Excel table. In this article, you will learn 7 methods to remove an AutoFilter if it exists in Excel using VBA scripts.
Use Excel VBA to Remove AutoFilter If It Exists: 7 Examples
1. Remove AutoFilter from Active Worksheet If It Exists
The following screenshot shows an AutoFilter in action in the active worksheet. We will remove this AutoFilter using the VBA code.
If you want to remove an AutoFilter from the active worksheet then follow the steps below:
❶ First of all, press ALT + F11 to open the VBA Editor.
❷ Then 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
- Here, I created a Sub procedure Public Sub RemoveAFActiveWorksheet
- Next, I used an IF statement to check if there exists any AutoFilter if exists it will remove the AutoFilter as I 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.
Now you will see the AutoFilter has been removed and all the data are visible now.
2. Using VBA to Delete AutoFilter from All Worksheets
Look at the following picture. You can see that AutoFilter is applied on both worksheets. Now we will write VBA code to remove an AutoFilter from all the worksheets in a workbook.
To delete the AutoFilter from all the worksheets in a workbook follow the steps below:
❶ First of all, press ALT + F11 to open the VBA Editor.
❷ Then 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
- Here, I used a For loop to search for the AutoFilter in each worksheet.
- Next, I 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.
Now you will see all the AutoFilter icons are removed from all the worksheets in your workbook just like the picture below:
3. Clear AutoFilter from a Single Column of a Table
In the picture below, you can see that AutoFilter is applied only in the first column of the table. In this method, we will write code to clear the AutoFilter from a single column of a table in Excel.
To do that,
❶ First of all, press ALT + F11 to open the VBA Editor.
❷ Then 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❹ After that, Paste and Save the code in the VBA Editor.
Breakdown of the Code
- First, I declared 3 variables.
- Then I inserted the table name and Set the sheet name.
- After that, I used the ListObjects property to make visible all the contents of a table.
- Lastly, I 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.
After that, you will see the AutoFilter exists no more in the first column of your table.
4. Remove AutoFilter from Multiple Columns of a Table
The following picture shows two AutoFilter icons in the first and second columns of a table. You can follow this method to remove AutoFilter from two columns of a table using VBA.
Now follow the steps below to clear AutoFilter if it exists from multiple columns in an Excel Table.
❶ First of all, press ALT + F11 to open the VBA Editor.
❷ Then 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
- First, I declared 3 variables.
- Then I inserted the table name and Set the sheet name.
- After that, I used the ListObjects property to make visible all the contents of a table.
- Lastly, I input two table column indexes using the AutoFilter Field
❺ Now, go back to the worksheet and press ALT + F11 to open the Macro dialog box.
❻ Select the macro DeleteAFMultiColumnsfromTable and hit the Run button.
After running the code, you will see the AutoFilter is removed from multiple columns.
5. Clear AutoFilter from an Entire Table Using Excel VBA
If you have the AutoFilter applied in your table just like the picture below and want to remove them all, then go along with this method.
❶ First of all, press ALT + F11 to open the VBA Editor.
❷ Then 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
- First, I declared 3 variables.
- Then I inserted the table name next Set the sheet name as ActiveSheet.
- After that, I used the ListObjects property to make visible all the contents of a table and used the Set statement to store it in xTable2.
- Lastly, I 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.
Now the AutoFilter will be removed from the entire table just like in the picture below:
6. Delete AutoFilter from Password Protected Worksheet If Exists
The following dataset shown in the picture below has AutoFilter activated and is protected with a password. Which is 7878.
In this method, you will get a VBA code to clear out AutoFilter from a password-protected worksheet like this.
Now follow the steps below:
❶ First of all, press ALT + F11 to open the VBA Editor.
❷ Then 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
- First, I declared a variable and input the password.
- Then in the ActiveSheet, I assigned the User Password to the Unprotect Password.
- Used ShowAllData property to unhide everything.
- Then I 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.
After that, the AutoFilter will be removed and all the records will be visible like this:
7. Use of VBA to Remove AutoFilter from Protected Worksheet without Password
The following picture shows a protected dataset without a password. In this method, we will write a code to remove AutoFilter from a passwordless-protected worksheet.
Now follow the steps below:
❶ First of all, press ALT + F11 to open the VBA Editor.
❷ Then 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
- First, I applied the With statement in the ActiveSheet and used the Unprotect property to unlock.
- Then, I used the ShowAllData property to unhide everything.
- Then I 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.
Finally, the AutoFilter will be cleared out and all the data will be visible like this:
Read More: Excel VBA to Check If AutoFilter is On
Practice Section
You will get an Excel sheet like the following screenshot, at the end of the provided Excel file. Where you can practice all the methods discussed in this article.
Download Practice Workbook
You can download the Excel file from the following link and practice along with it.
Conclusion
To sum up, we have discussed 7 methods to remove Autofilter if it exists using VBA in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries ASAP.
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
