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 of removing the filter in Excel VBA.
How to Remove Filter in Excel VBA: 5 Simple Methods
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, 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.
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.
- 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.
- 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 ListObjectVariable 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.ShowAllDataThis line of code will remove all filters for the entire data.
End SubThis will end the procedure.
Read More:Â Filter Multiple Criteria in Excel with VBA (Both AND and OR Types)
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 lstObjThose lines of code loop through all tables on the sheet and remove all filters for the entire worksheet.
Read More: How to Filter Based on Cell Value Using Excel VBA
3. Remove Filter from a Column with VBA in Excel
Let’s have a glance at another way to clear the 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:=4This code line specifies the field number only and no other parameters.
Read More: Excel VBA: How to Filter with Multiple Criteria in Array
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: Excel VBA to Filter in Same Column by Multiple Criteria
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 shtnamThe 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.
Download Practice Workbook
You can download the workbook and practice with them.
Conclusion
The above methods will assist you to Remove Filters in Excel VBA. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section.
Related Articles
- Excel VBA: Filter Based on Cell Value on Another Sheet
- Excel VBA: Filter Table Based on Cell Value
- VBA Code to Filter Data in Excel
- Filter Different Column by Multiple Criteria in Excel VBA
