How To Clear Filters with VBA Macros in Excel (With Code)

Excel’s filtering capability is particularly valuable when working with large datasets. It provides a more focused and tailored view of the information at hand.

However, when it comes to managing filters on a larger scale, manually clearing them can be time-consuming and hectic.

That’s where Excel’s built-in programming language, Visual Basic, comes to the rescue.

In this post, we will be leveraging VBA to automate the process of clearing filters, saving valuable time and effort.

clear-filters-with-vba-macros

Why Use VBA to Clear Filters?

You can save time and effort if you use VBA to clear filters with a single click or keystroke.

Let’s assume you are a member of the sales team receiving a monthly sales report with thousands of rows and columns. You apply filters to only specific product categories, sales regions, or periods, allowing for practical data analysis.

However, once the report is complete, manually clearing the filters can be time-consuming, particularly if you need to repeat the procedure several times throughout the day or week.

Check out the various types of Filter functions available in Excel. Which one are you using?

Here are some reasons of using VBA to remove auto filters if exists in your worksheet:

  • Scalability
  • Accuracy and Consistency
  • Efficiency and Automation
  • Reliable Task Management

How To Clear Filter in Excel Using VBA Macros

There are 3 methods of removing filters in Excel using VBA, out of which the AutoFilter method is the best one. We only use this method while clearing filters on a single Table column by specifying the Field parameter.

First, enable VBA in Excel so that you don’t run into any errors.

Let’s look at the methods in details:

Method 1: Use The AutoFilter Method to Clear Filter on a Single Column

This method is also applicable for removing filters for a particular range. First let’s see the code for the AutoFilter Method and then move on to the procedure.

VBA AutoFilter Code

Sub ClearColumnFilterRange()
    Sheet1.Range("B3:G1000").AutoFilter Field:=4
End Sub

Explanation:

This code performs the task of clearing the filter from cells B3 to G1000. Apart from that, Field:=4 specifies the field number of the column from which the filter will be cleared. In this case, the number 4 represents the fourth column in the range (column D).

You can modify the range of cells and field number according to your preferences.

vba-clear-filter-from-one-column

Here are the steps to remove filters using the auto filter method:

  1. Go to the Developer Tab and select the Visual Basic Option.
  2. Insert a New Module and paste the autofilter code.
  3. Save the code using the Save Button.
  4. Run the code using F5.

Once you have saved the VBA code, it will be recorded as a macro. The next time you open the file, simply go to the Developer Tab, click on Macros, select the ClearColumnFilterRange subroutine, and press the Run button to utilize the code.

Note: Ensure that the Excel worksheet is Macro Enabled. Click on the Files Option, select the Save As button. Click on File Format and select Excel macro-enabled Workbook (.xlsm).

Method 2: Clear Multiple Filters Simultaneously

You should implement this method only If you have multiple filters applied in different columns and want to clear them all at once.

VBA Code to clear multiple filters at once:

Sub ClearAllFilters()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    ws.AutoFilterMode = False
End Sub

Explanation of the Code:

This code is designed to clear all filters applied to a specific worksheet within the workbook. Firstly, you will see a declaring variable named “ws” of the type Worksheet. This variable is used to refer to the worksheet where the filters need to be cleared.

Next is the Set statement. It is used to assign a specific worksheet to the “ws” variable. In this case, the worksheet assigned is “Sheet1”. You can modify the sheet name according to your workbook.

vba-autofilter-select-all

The next line, ws.AutoFilterMode = False, sets the AutoFilterMode property of the specified worksheet to False. This is done for removing multiple filters applied to the worksheet.By setting the AutoFilterMode property to False, all existing filters in the specified worksheet are deactivated.

How to use this method?

Process:

Press Alt+F11 on your keyboard simultaneously. This will open the Visual basic editor. Click on the Insert option and select Module. Now, paste the code inside the module and save it. Click on the Run button to apply the code.

Method 3: Remove all Filters from an Excel Table

Like the heading says, this method is only applicable if you have generated a Table from Excel. Don’t know how to create a table array argument in Excel? Then I recommend you to check the post first.

For this step, we will be using the ShowAllData method. Like the AutoFilter method, ShowAllData is a property of the ListObject.

VBA Code:

Sub ClearAllTableFilters()
    Dim tbl As ListObject
       ' Set the table object to the active table in the worksheet
    Set tbl = ActiveSheet.ListObjects(1)
    ' Check if the table has filters applied
    If tbl.AutoFilter.FilterMode Then
        ' Clear all filters
        tbl.AutoFilter.ShowAllData
    End If
End Sub

Explanation of the Code:

The ShowAllData function is used in conjunction with the AutoFilter functionality to clear all filters applied to a table in Excel.

Auto Filter and Show all Data filters work collaboratively. The AutoFilter method allows us to apply filters to specific columns within a table, enabling us to display only the data that meets certain criteria. This is because, when filters are applied, some rows are hidden, showing only the filtered subset of data.

showalldata-vba

To clear the filters and display all the data again, the ShowAllData method is used. It removes any applied filters and shows the complete unfiltered dataset within the table.

Click Alt+F11 to open the VB Editor. Paste the code there and then save it. Select the Macros Button from the Developer Tab and click on ClearAllTableFilters since it is set as a subroutine for this code.

ShowAllData and AutoFilter Function Missing in Excel VBA?

ShowAlldata and Autofilter functions are missing in your Excel file because Excel tables have a different filtering mechanism compared to regular ranges.

The two functions mentioned here are applicable to table objects, not worksheet ranges.

To solve this problem, select the range of data you are considering as a table, then go to the Insert tab. Select Table and click on “My Table has headers” (optional). Now if you want, you can make your excel tables look good. Or, you can directly paste the code to the VBA Editor.

How To Add Filters in Excel Using VBA

Adding Filters in large datasets is time-consuming if you do it in the manual way. However, VBA always comes to the rescue. Using a simple code, you can add filters automatically.

Here are the steps to add filters in Excel using VBA:

  1. Select the dataset and go to the Developers Tab.
  2. Click on Visual Basic and, beside the Excel logo, press on Insert Module.
  3. Paste the following code and run it.
Sub AddFilter()
    Dim ws As Worksheet
    Dim rng As Range
    Set ws = ThisWorkbook.Sheets("Sheet1")
    Set rng = ws.Range("B2:C7")
    rng.AutoFilter Field:=2, Criteria1:="Filter Criteria"
End Sub

Variables that need modifications:

vba-add-filter

  1. Set the Worksheet name based on the name of your current sheet.
  2. Set the range of the cells that need filters.
  3. Select Field And Filter Criteria. The Field parameter specifies the column number (1 for the first column, 2 for the second column, etc.) on which you want to apply the filter.

Advanced Tips of Filtering Data Using VBA

  • Always save a backup copy of your workbook before applying VBA Codes.
  • Make sure your workbook is Macro-enabled.
  • Add a macro button so that you can automate your task with a single click.
  • Assign suitable and easy names for the Macros.
  • Test your VBA code on a sample dataset before applying it to a large dataset.

Conclusion

Adding and removing filters are necessary for a more tailored and in-depth view of a large dataset. By mastering the automatic filtering process, you can save time, increase efficiency, and focus on analyzing your data rather than manual filtering.

Related Posts