Bottom line: Learn how to clear all filters, and filters on a single column with VBA macros.  Includes code examples for regular ranges and Excel Tables.

Skill level: Intermediate

VBA Code to Clear Filters in Excel

Download the File

The Excel file that contains the code can be downloaded below.  This file contains code for filtering different data types and filter types.  Please see my article on The Ultimate Guide to AutoFilters in VBA for more details.

VBA AutoFilters Guide.xlsm (100.5 KB)

Clear All Filters from a Range

We use the ShowAllData method to clear all filters applied to a range.

This is the same as clicking the Clear button on the Data tab of the ribbon (keyboard shorcut: Alt, A, C)

Clear All Filters on Sheet or Table with ShowAllData Method in VBA

Only one filter range can be applied to a worksheet, so we are actually clearing the filters on the sheet.

Sub Clear_All_Filters_Range()

  'To Clear All Fitlers use the ShowAllData method for
  'for the sheet.  Add error handling to bypass error if
  'no filters are applied.  Does not work for Tables.
  On Error Resume Next
  On Error GoTo 0
End Sub

ShowAllData Method Error

If there are no filters are applied to any column, then the ShowAllData method will raise an error.  It's a Run-time ‘1004' error with the description: Method ‘ShowAllData' of object ‘_Worksheet' failed.

VBA Clear Filters Error Method ShowAllData of Object Worksheet failed

The On Error Resume Next line will bypass that error.  On Error GoTo 0 resets that so errors are raised in any lines of code below.

Note: When the ShowAllData method is referenced as a member of the sheet, it does NOT clear filters that are applied to Excel Tables (ListObjects) unless a cell is selected in the Table.  Therefore, it's best to use the code below for Tables.

Clear All Filters from an Excel Table

To clear all filters on an Excel Table (ListObject) we also use the ShowAllData method.  In this case, ShowAllData is a member of the AutoFilter property of the ListObject object.

Sub Clear_All_Filters_Table()

Dim lo As ListObject
  'Set reference to the first Table on the sheet
  Set lo = Sheet1.ListObjects(1)
  'Clear All Filters for entire Table

End Sub

Clear All Filter from All Tables on a Sheet

The code above will only clear filters for a single Table.  We can loop through the Tables on the sheet to clear all filters from each Table.

Sub Clear_All_Table_Filters_On_Sheet()

Dim lo As ListObject
  'Loop through all Tables on the sheet
  For Each lo In Sheet1.ListObjects
    'Clear All Filters for entire Table
  Next lo

End Sub

Clear Filters on a Single Column

To clear filters on a single column we use the AutoFilter method. We only reference the Field parameter and set the value to the number of the column we want to clear.

Clear Filter on Single Column VBA AutoFilter Method Field Only

Sub Clear_Column_Filter_Range()
  'To clear the filter from a Single Column, specify the
  'Field number only and no other parameters
  Sheet1.Range("B3:G1000").AutoFilter Field:=4

End Sub

The Field is the column number of the range the filters are applied to, NOT the column number of the worksheet.

Field Parameter Value is Column Number of the Range or Table

The same technique is use to clear filters applied to a column in a Table.  In this case the AutoFilter method is a member of the Range object of the ListObject.

Sub Clear_Column_Filter_Table()

Dim lo As ListObject
  'Set reference to the first Table on the sheet
  Set lo = Sheet1.ListObjects(1)
  'Clear filter on Single Table Column
  'by specifying the Field parameter only
  lo.Range.AutoFilter Field:=4
End Sub

Filters & Data Types

The filter drop-down menu options change based on what type of data is in the column.   We have different filters for text, numbers, dates, and colors.  This creates A LOT of different combinations of Operators and Criteria for each type of filter.

I created separate posts for each of these filter types.  The posts contain explanations and VBA code examples.

The file in the downloads section above contains all of these code samples in one place.  You can add it to your Personal Macro Workbook and use the macros in your projects.

Please leave a comment below with any questions or suggestions.  Thanks! 🙂


Your email address will not be published. Required fields are marked *

  • Thank you for the great run throughs.
    Could you add a scenario to handle if the filters are off – happens for me sometimes when I use powerquery that the autofilters are off, so when I run .ShowAllData it throws an error.
    The problem is that it’s intermittent so i can’t just run .AutoFilter because if it happens to already be on, then I turn it off and get an error since i’m trying to show all data with autofilter off.

  • Hello,

    I have multiple users at different locations that use a shared spreadsheet. Is there a VBA code that would clear upon saving and closing the document any filters a person applied , BUT STILL RETAIN THE FUNCTION OF FILTERING? I have a code to clear filtering upon saving and closing, but that also removes the arrows for future filtering when the spreadsheet is opened again.

  • I lose quite a lot of time on forums to find a solution to clear filter in a listobjects. Your solution work properly.
    Thanks you very much!

Generic filters
Exact matches only
Filter by Custom Post Type


Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...