Excel VBA: Remove AutoFilter If It Exists (7 Examples)

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.


Download Practice Workbook

You can download the Excel file from the following link and practice along with it.


7 Examples of Using VBA to Remove AutoFilter If It Exists in Excel

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.

Insert Module to Remove AutoFilter If It Exists

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.

Remove AutoFilter from Active Worksheet If It Exists Using Excel VBA

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 RemoveAFActiveWorksheet and hit the Run button.

Macro: Remove AutoFilter from Active Worksheet If It Exists Using Excel VBA

Now you will see the AutoFilter has been removed and all the data are visible now.

Result: Remove AutoFilter from Active Worksheet If It Exists Using Excel VBA

Read More: Excel VBA to Check If AutoFilter is On (4 Easy Ways)


2. Using VBA to Delete AutoFilter from All Worksheets

Look at the following picture. You can see the 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.

Insert Module to Remove AutoFilter If It Exists

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.

Delete AutoFilter from All Worksheets Using Excel VBA

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.

Macro: Delete AutoFilter from All Worksheets Using Excel VBA

Now you will see  all the AutoFilter icons are removed from all the worksheets in your workbook just like the picture below:

Output: Delete AutoFilter from All Worksheets Using Excel VBA


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.

Input: Use of VBA to Clear AutoFilter from a Single Column of a Table

To do that,

❶ First of all, press ALT + F11 to open the VBA Editor.

❷ Then go to Insert >> Module.

Insert Module to Remove AutoFilter If It Exists

❸ 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.

Use of VBA to Clear AutoFilter from a Single Column of a Table

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.

Insert Module to Remove AutoFilter If It Exists

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.

Remove AutoFilter If It Exists from Multiple Columns of a Table using Excel VBA

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.

Result: Remove AutoFilter If It Exists from Multiple Columns of a Table using Excel VBA

Read More: [Fix]: AutoFilter Method of Range Class Failed (5 Solutions)


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.

Insert Module to Remove AutoFilter If It Exists

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.

Clear AutoFilter from an Entire Table If It Exists Using Excel VBA

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 the picture below:

Result: Clear AutoFilter from an Entire Table If It Exists Using Excel VBA


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.

Insert Module to Remove AutoFilter If It Exists

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.

Use of VBA to Delete AutoFilter from Password Protected Worksheet If It Exists

Breakdown of the Code

  • First, I declared a variable and input the password.
  • Then in the ActiveSheet, I assigned 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.

Insert Module to Remove AutoFilter If It Exists

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.

Remove AutoFilter from Protected Worksheet without Password Using Excel VBA

Breakdown of the Code

  • First, I applied the With statement in the ActiveSheet and used 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:


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.


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. And please visit our website Exceldemy to explore more.


Related Articles

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo