Here is a fancy filter trick, if you frequently have to filter your Excel tables. Click on a cell in this Excel table, and the column is automatically filtered for that item. In the worksheet shown below, columns B and C have already been filtered, and when I click on Pen Set, column D will only show that item.
.
Clear the Filter
To see all the items in a column again, just click on the heading cell, and the filter for that field is cleared.
FilterClick Sample File
This filtering trick is accomplished with a bit of programming. There is an event procedure – Worksheet_SelectionChange – that automatically runs when you select a cell on the worksheet .
You can turn the FilterClick feature on or off, by clicking on a cell that’s named FilterStatus. When you click on that cell, its value automatically toggles between On and Off.
The SelectionChange procedure checks the value in that cell, and only filters the column if the FilterStatus cell says “On”.
Update for Working With Excel Tables
I created the sample file a few years ago, and have just updated it to include a worksheet that has the data in a formatted Excel table. The code is almost identical – only one line is different if you’re using a table.
In the sample code shown below, there are two versions of the line that sets the range rngF. For formatted tables, use the first line, and for non-table AutoFilters, use the second line.
- Set rngF = ActiveSheet.ListObjects(1).Range ‘for tables
- ‘Set rngF = ActiveSheet.AutoFilter.Range ‘for AutoFilter ranges
Type an apostrophe at the start of the line that you don’t need, and remove the apostrophe at the start of the line that you do need.
You can see more examples of List AutoFilter VBA here.
The FilterClick Code
Here is the code, and you can copy it to the worksheet module for the sheet that your list is on. Remember to fix the Set rngF lines, so one has an apostrophe, and one doesn’t – or delete the line that you don’t need.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim rngF As Range Dim rngFS As Range Dim lRow As Long Dim lCol As Long Set rngF = ActiveSheet.ListObjects(1).Range 'for tables 'Set rngF = ActiveSheet.AutoFilter.Range 'for AutoFilter ranges Set rngFS = ActiveSheet.Range("FilterStatus") lCol = rngF.Columns(1).Column - 1 lRow = rngF.Columns(1).Row If Target.Count > 1 Then GoTo exitHandler If Target.Address = rngFS.Address Then If rngFS.Value = "On" Then rngFS.Value = "Off" Else rngFS.Value = "On" End If End If If UCase(rngFS.Value) = "ON" Then If Not Intersect(Target, rngF) Is Nothing Then If Target.Row > lRow Then rngF.AutoFilter Field:=Target.Column - lCol, _ Criteria1:=Target.Value ElseIf Target.Row = lRow Then rngF.AutoFilter Field:=Target.Column - lCol End If End If End If exitHandler: Exit Sub End Sub
Download the Sample File
You can download the sample file, with the code for both versions – table and non-table – on my Contextures website.
Go to the Excel Sample Files page, and in the Filters section, look for FL0021 – FilterClick.
_______________
I have a excel 2007 spreadsheet that has 7 row and 10 colums. 5 of those columns has a multiple select dropdown list and the dropdown list has the ability to select multiple items. Thank you Contextures! The problem I am having is that, when I protect the spreadsheet and lock down column C, where I have the (=TODAY())CODE, the spreadsheet looses the ability to multi-select from the dropdown lists.
@Wanda, the code should work, even if the sheet is protected. Is there other code on the worksheet module? Maybe there’s another line that isn’t able to run, and it’s exiting the procedure before it gets to the multi-selection code.
Debra,
This code is amazing! Looking forward to adding it to future reports and looking like a excel wizard.
Thanks!
Hi,
this is amazing but i have a question: is it possible clicking a cell in one sheet, to filter the values in another sheet?
yes, that is interesting also for me
Yes, this is very very powerful. I also have the same request as Frozen. Clicking on a cell in one sheet would lead to a filter in another sheet would be IDEAL!!
Is it possible to make something like this but when you click on value in Sheet1 to apply that value on filter in Sheet2?
Is it possible to make something like this but when you click on value in Sheet1 to apply that value on filter in Sheet2?