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. Click 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 click a cell outside the Excel defined Table to clear filter conditions.
VBA Event code
The following Event code is executed 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 click the sheet where the Excel defined Table is located in the project explorer window
- Copy / Paste vba code to sheet module
- Return to excel
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies [โฆ]
Open Excel files in a folder [VBA]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The [โฆ]
Split data across multiple sheets [VBA]
In this post I am going to show how to create a new sheet for each airplane using vba. The [โฆ]
Working with COMBO BOXES [Form Controls]
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible [โฆ]
Identify missing numbers in a column
The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower [โฆ]
Change chart data range using a Drop Down List [VBA]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above [โฆ]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The [โฆ]
Run a Macro from a Drop Down list [VBA]
This article demonstrates how to execute a VBA macro using a drop down list. The drop down list contains multiple [โฆ]
How to create an interactive Excel chart [VBA]
This article describes how to create an interactive chart, the user may click on a button or multiple buttons and [โฆ]
What's on this page Copy a file Copy and rename a file Rename a file List files in a folder [โฆ]
One Response 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.
English
Spanish
[...] 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 [...]