Filter an Excel defined Table based on selected cell [VBA]
In this post I am going to demonstrate how to quickly apply a filter to a table. I am using the selection change event to apply the filter. Press with mouse on a cell in a table and the cell value is instantly used as a filter in the current table column.
Select a cell outside the table and all table filters are removed. Unfortunately, you can't select multiple cells in a table column in this macro.
Nothing in the table is selected, see picture above.
Cell B4 is selected in the picture above and the Excel defined Table is instantly filtered based on cell B4.
This example shows cell C4 selected. Now are both values in cell B4 and C4 are now used as filtering parameters, simply press with left mouse button on a cell outside the Excel defined Table to clear filter conditions.
VBA Event code
The following Event code is rund every time a cell is selected.
'To use this Event the name must be Worksheet_SelectionChange(ByVal Target As Range) Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'Declare variables and data types Dim ACell As Range Dim ActiveCellInTable As Boolean Dim Scol As Single 'Enable error handling On Error Resume Next 'Save cell to object ACell Set ACell = Selection Save Boolean value TRUE or FALSE to ActiveCellInTable based on if Table name is empty or not. ActiveCellInTable = (ACell.ListObject.Name <> "") 'Disable error handling On Error GoTo 0 'Check if ActiveCellInTable equals to False meaning if selected cell is outside the Excel defined Table If ActiveCellInTable = False Then 'Go through ListObjects (Excel defined Tables) For i = 1 To ActiveSheet.ListObjects.Count 'Iterate through columns For j = 1 To ActiveSheet.ListObjects(i).Range.Columns.Count 'Clear filter conditions for each column ActiveSheet.ListObjects(i).Range.AutoFilter Field:=j Next j Next i 'Stop Event code Exit Sub End If 'Make sure that only one cell is selected or stop event code If Selection.Cells.Count > 1 Then Exit Sub 'Calculate relative column number in the Excel defined Table Scol = Selection.Column - ACell.ListObject.Range.Column + 1 'Apply filter condition to given column ActiveSheet.ListObjects(ACell.ListObject.Name).Range.AutoFilter _ Field:=Scol, Criteria1:="=" & Selection End Sub
Where to copy the code?
- Press Alt+F11 to open the VB Editor
- Double press with left mouse button on the sheet where the Excel defined Table is located in the project explorer window
- Copy / Paste vba code to sheet module
- Return to excel
Macro category
This article demonstrates how to add or remove a value in a regular drop down list based on a list […]
In this tutorial, I am going to show you how to add values to a drop down list programmatically in […]
This article demonstrates how to place values automatically to a table based on two conditions using a short macro. Cell […]
Excel categories
2 Responses to “Filter an Excel defined Table based on selected cell [VBA]”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
[...] Make sure you have selected the correct month to be able to see them.Recommended blog postsFilter a table using the selection change eventExtract unique distinct values from a filtered table (udf and array formula)Quickly filter a column [...]
I need help in coding having below conditions while filter items there are about 1 lakh+ records in a table without any additional column in table.
1. Select all between 1000 and 1500
2. Select 1800,1820,1825
3. Select all between 2000 and 2200
4. Select all above 2800