1

How to Clear Filters with VBA Macros

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
    Sheet1.ShowAllData
  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
  lo.AutoFilter.ShowAllData

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
    lo.AutoFilter.ShowAllData
    
  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! 🙂

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 1 comments
sandeep - November 1, 2018

Dear Jon, This is outstanding.

Reply

Leave a Reply: