VBA Remove Filter from Table in Excel
VBA remove filter from table in Excel. In this tutorial we learn how to remove or clear or delete filter from table. We use AutoFilter and ShowAllData method. It helps to show or display complete data for table. Let us see syntax, different examples using VBA. Also find and learn step by step instructions to run vba example macro code.
- Overview to Remove Filter from Table in Excel
- VBA Syntax to Remove Filter from Table in Excel
- VBA Remove Filter from Table
- VBA Delete Filter on Single Column from Table
- Clear Filter on Multiple Columns From Table
- Clear All Filters from All Tables on Worksheet
- Clear All Filters from All Tables on the Workbook
- Other Related References
VBA Syntax to Remove or Clear Filter from Table in Excel
Let us see the syntax to remove filter from table in Excel.
expression.AutoFilter.ShowAllData
Where
expression represents ListObject.
AutoFilter represents object.
ShowAllData represents method of the AutoFilter object.
VBA Remove Filter from Table
Here is a simple example macro to remove filter from table.
'Remove Filter from Table in Excel VBA Sub VBAF1_Remove_filter_from_Table() 'Declare Variables Dim oSheetName As Worksheet Dim sTableName As String Dim loTable As ListObject 'Define Variable sTableName = "MyTable" 'Define WorkSheet object Set oSheetName = Sheets("Table") 'Define Table Object Set loTable = oSheetName.ListObjects(sTableName) 'Add filter to table loTable.AutoFilter.ShowAllData End Sub
Delete Filter on Single Column from Table
Let us see another example delete filter on single column from table in Excel VBA. Here we have to specify column number to delete filter from table.
'VBA Delete Filter on Single Column from Table Sub VBAF1_Delete_filter_OnSingle_Column_from_table() 'Declare Variables Dim oSheetName As Worksheet Dim sTableName As String Dim loTable As ListObject 'Define Variable sTableName = "MyTable" 'Define WorkSheet object Set oSheetName = Sheets("Table") 'Define Table Object Set loTable = oSheetName.ListObjects(sTableName) 'Add filter to table loTable.Range.AutoFilter Field:=3 End Sub
Clear Filter on Multiple Columns From Table in Excel VBA
Here is one more example clear filter on multiple columns from table in Excel VBA. Here we have to specify multiple column numbers to clear filter from table.
'Clear Filter on Multiple Columns From Table Sub VBAF1_Clear_filter_OnMultiple_Columns_From_Table() 'Declare Variables Dim oSheetName As Worksheet Dim sTableName As String Dim loTable As ListObject 'Define Variable sTableName = "MyTable" 'Define WorkSheet object Set oSheetName = Sheets("Table") 'Define Table Object Set loTable = oSheetName.ListObjects(sTableName) loTable.Range.AutoFilter Field:=2 loTable.Range.AutoFilter Field:=3 End Sub
Clear All Filters from All Tables on Worksheet in Excel VBA
Let us see another example to clear filters from all tables on worksheet in Excel VBA. We loop through all the tables worksheets on the worksheet.
'Clear All Filters from All Tables on Worksheet Sub VBAF1_Clear_All_Filters_From_All_Tables_OnWorksheet() 'Declare Variables Dim oSheetName As Worksheet Dim loTable As ListObject 'Define WorkSheet object Set oSheetName = Sheets("Table") 'Loop Through All Tables on the Worksheet For Each loTable In oSheetName.ListObjects 'Clear Filter from the Table loTable.AutoFilter.ShowAllData Next loTable End Sub
Clear All Filters from All Tables on the Workbook using VBA
One more example to clear filters from all tables on the worksheet using Excel VBA. We loop through all the tables in all worksheets on the worksheet.
'Clear All Filters from All Tables on the Workbook Sub VBAF1_Clear_All_Filters_From_All_Tables_InWorkbook() 'Declare Variables Dim oSheetName As Worksheet Dim loTable As ListObject 'Loop Through All Tables in the Workbook For Each oSheetName In Worksheets 'Loop Through All Tables on the Worksheet For Each loTable In oSheetName.ListObjects 'Clear Filter from the Table loTable.AutoFilter.ShowAllData Next loTable Next oSheetName End Sub
Step by Step Instructions to use above specified VBA macro codes
Here are the instructions to use above macro in Visual basic editor.
- Open Visual Basic Editor(VBE) by clicking Alt +F11
- Go to code window by clicking F7
- Copy above specified macro or procedure
- Paste above copied code in code window
- Run macro by clicking F5 or Run command
- See output screenshots after procedure.
Other Related References
You can also learn complete details like syntax, example and etc by clicking on the following buttons.