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.
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.
- 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.
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 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
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.
This line of code will remove all filters for the entire data.
This will end the procedure.
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.
- 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.
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.
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.
- 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.
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
This code line specifies the field number only and no other parameters.
- Excel VBA: How to Filter with Multiple Criteria in Array (7 Ways)
- VBA Code to Filter Data by Date in Excel (4 Examples)
- How to Use Filter in Protected Excel Sheet (With Easy Steps)
- Filter Different Column by Multiple Criteria in Excel VBA
- VBA Code to Filter Data in Excel (8 Examples)
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.
- 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.
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.
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.
- 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.
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.
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!