How to Remove Filter in Excel VBA (5 Simple Methods)

While working with Microsoft Excel, filtering data allows us to see only the information that we want. Whenever we want to concentrate on certain elements in a large dataset or tables, this technique comes in handy. When the work is done, we need back those data in our spreadsheet. Although Excel already has a built-in tool for this. But VBA is the most efficient, time-saving, and secure way to conduct any task in Excel. In this article, we will some examples to remove the filter in Excel VBA.


Download Practice Workbook

You can download the workbook and practice with them.


5 Simple Methods to Remove Filter in Excel VBA

Excel has built-in tools and functions to remove filters from data. But with Excel VBA we can quickly remove those filters just by running the VBA code. To remove filters from data we are going to use the following dataset. The dataset contains some product IDs in column B, product names in column C, and the delivery country in column D. As we want to see only the details of the product Shampoo and Conditioner, so we filtered them. Now, suppose we need to clear those filtered data. We will use some Excel VBA Macros for this. Let’s demonstrate the examples to clear those filters from the data using Excel VBA.

5 Methods to Remove Filter in Excel VBA


1. Apply VBA to Remove All Filters From Excel Table

With Excel VBA, users can easily use the code which acts as excel menus from the ribbon. To use the VBA code to remove all filters from the Excel table, let’s follow the steps down.

STEPS:

  • Firstly, go to the Developer tab from the ribbon.
  • Secondly, from the Code category, click on Visual Basic to open the Visual Basic Editor. Or press Alt + F11 to open the Visual Basic Editor.

Apply VBA to Remove All Filters From Excel Table

  • Instead of doing this, you can just right-click on your worksheet and go to View Code. This will also take you to Visual Basic Editor.

Apply VBA to Remove All Filters From Excel Table

  • This will appear in the Visual Basic Editor where we write our codes to create a table from range.
  • Thirdly, click on Module from the Insert drop-down menu bar.

  • This will create a Module in your workbook.
  • And, copy and paste the VBA code shown below.

VBA Code:

Sub Remove_Filters1()
Dim lstObj As ListObject
  Set lstObj = Sheet1.ListObjects(1)
  lstObj.AutoFilter.ShowAllData
End Sub
  • After that, run the code by clicking on the RubSub button or pressing the keyboard shortcut F5.

  • And, finally, following the steps will remove all the filters from the excel table on your worksheet.

VBA Code Explanation

Sub Remove_Filters1()

Sub is a part of code that is used to handle the work in the code but will not return any value. It is also known as subprocedure. So we name our procedure Remove_Filters1().

Dim lstObj As ListObject

Variable declaration.

Set lstObj = Sheet1.ListObjects(1)

VBA Set simply allows us to avoid having to type in the range we need to pick over and over again when running the code. So, we set the reference to the first table on the sheet.

lstObj.AutoFilter.ShowAllData

This line of code will remove all filters for the entire data.

End Sub

This will end the procedure.

Read More: How to Remove Filter in Excel (5 Easy & Quick Ways)


2. Clear All Excel Table Filters on a Sheet Using VBA

Let’s look at another example of utilizing Excel VBA to remove all excel table filters on a sheet. For this, follow the steps below.

STEPS:

  • First, go to the Developer tab from the ribbon.
  • Second, click on Visual Basic to open the Visual Basic Editor.
  • Another way to open the Visual Basic Editor is simply to press Alt + F11.
  • Or, right-click on the sheet, then select View Code.
  • Next, go to Insert and select Module from the drop-down menu.
  • And, this will open up the visual basic window.
  • After that, copy and paste the VBA code below.

VBA Code:

Sub Remove_Filters2()
Dim lstObj As ListObject
  For Each lstObj In Sheet2.ListObjects
    lstObj.AutoFilter.ShowAllData
  Next lstObj
End Sub
  • Further, press the F5 key or click on the Run Sub button to run the code.

  • And, this code will clear all excel table filters from your sheet and give the output like Method 1.

VBA Code Explanation

For Each lstObj In Sheet2.ListObjects
    lstObj.AutoFilter.ShowAllData
Next lstObj

Those lines of code loop through all tables on the sheet and remove all filters for the entire worksheet.

Read More: How to Filter Excel Pivot Table (8 Effective Ways)


3. Remove Filter from a Column with VBA in Excel

Let’s have a glance at another way to clear filter from a column with Excel VBA. Let’s see the procedure for this.

STEPS:

  • To begin, click the Developer tab on the ribbon.
  • Second, launch the Visual Basic Editor by clicking on Visual Basic.
  • Alternatively, you may access the Visual Basic Editor by pressing Alt + F11.
  • Or, right-click on the sheet and choose View Code from the menu.
  • Next, pick the Module from the drop-down box under Insert.
  • And the visual basic window will appear.
  • Write the code there.

VBA Code:

Sub Remove_Filter3()
  Sheet1.Range("B3:D16").AutoFilter Field:=4
End Sub
  • Finally, press the F5 key to run the code.

  • Using this code will remove the filter from a column in your excel table.

VBA Code Explanation

Sheet1.Range("B3:D16").AutoFilter Field:=4

This code line specifies the field number only and no other parameters.

Read More: Excel VBA to Filter in Same Column by Multiple Criteria (6 Examples)


Similar Readings


4. Clear All Filters in an Active Worksheet

Now, take a look at another Excel VBA method to clear all filters from an active worksheet. Let’s follow the steps down.

STEPS:

  • To start, open the ribbon and select the Developer option.
  • Then, to access the Visual Basic Editor, click on Visual Basic.
  • Pressing Alt + F11 will also bring up the Visual Basic Editor.
  • Alternatively, right-click the sheet and choose View Code from the menu that appears.
  • Now, from the Insert drop-down option, pick Module.
  • Then copy and paste the VBA code that follows.

VBA Code:

Public Sub RemoveFilter()
  If ActiveSheet.FilterMode = True Then
   ActiveSheet.ShowAllData
  End If
End Sub
  • Run the code by pressing the F5 key.

  • And, finally, you will be able to remove filters from your data using this VBA code like Method-1.

Read More: How to Filter by List in Another Sheet in Excel (2 Methods)


5. Excel VBA to Remove All Filters from a Workbook

Let’s explore another Excel VBA way to remove all filters from a workbook. So, let’s look at the steps down.

STEPS:

  • To begin, open the ribbon and choose Developer from the drop-down menu.
  • Then select Visual Basic to open the Visual Basic Editor.
  • The Visual Basic Editor may also be accessed by pressing Alt + F11.
  • Alternatively, you may right-click the sheet and select View Code from the pop-up menu.
  • After that, select Module from the Insert drop-down menu.
  • Then copy and paste the following VBA code.

VBA Code:

Sub Remove_Filters_From_Workbook()
    Dim shtnam As Worksheet
    Dim lstObj As ListObject
    For Each shtnam In Worksheets
        For Each lstObj In shtnam.ListObjects
          lstObj.AutoFilter.ShowAllData
        Next lstObj
    Next shtnam
End Sub
  • Finally, run the code by pressing F5 on your keyboard and you see the result in your worksheet.

  • This VBA code will clear all the filters from your whole workbook as shown in the first Method.

VBA Code Explanation

For Each shtnam In Worksheets
    For Each lstObj In shtnam.ListObjects
        lstObj.AutoFilter.ShowAllData
    Next lstObj
Next shtnam

The first loop is for looping through all tables in the workbook. The second loop is for looping through all tables on the worksheet. Then, the line inside the loop just clears the filter from the table. After that, close the loop with the last two lines.

Read More: Shortcut for Excel Filter (3 Quick Uses with Examples)


Conclusion

The above methods will assist you to Remove Filter in Excel VBA. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section. Or you can have a glance at our other articles in the ExcelDemy.com blog!


Related Articles

Sabrina Ayon

Sabrina Ayon

Hi there! This is Sabrina Ayon. I'm really excited to welcome you to my profile. Currently, I'm working in SOFTEKO as a Excel and VBA Content Developer. I'm a student of United International University and my program is Computer Science and Engineering. I love working with computers and solving problems. I’ve always been interested in research and development. So, here I will be posting articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo