Excel VBA: Remove AutoFilter If It Exists: 7 Methods

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.