Excel’s Filter by Selected Cell’s Value is a popular command, and for good reason. It’s a quick way to filter the data that you’re looking at. There are times when I’d like to take it one step further and filter by the values of multiple cells. So I wrote some code and added a “Filter By All Selected Values” item to my right-click menu. It works just like Filter by Selected Cells except you can select multiple cells.
Here’s the result:
There’s one situation where this feature is especially handy: that’s when I need to see all instances in one column of items that match a single characteristic in another column. For instance, “I want to see all the pie orders from customers who have ordered Red Velvet Cheesecake.”
I could do this operation with a Countif formula like…
and then filter to all the rows that return TRUE.
But this new routine allows me to do this without helper columns.
Here’s How
First, select “Red Velvet Cheesecake,” then right-click and choose the regular old Filter > Filter by Selected Cell’s Value. You’ll see all the rows with that value.
Next, select all the customers and run the “Filter by All Selected Values” code. This will filter the Customers column by only the three currently visible names. This is the part that I don’t see how to do easily without this code.
Finally, clear the filter from “Pies” that you set in the first step. The result will match that above with the COUNTIFS formula.
A Brief Commercial Announcement
If you’re wondering how I got those things in my context menu see the MenuRighter page.
The Code
The code I wrote works for tables and contiguous areas.
Dim FilterValues() As String
Dim i As Long, j As Long
Dim ColNumberInFilterRange As Long
Dim FilterRange As Excel.Range
Dim InTable As Boolean
Dim CollUniqueValues As Collection
Const MAX_FILTER_CELLS As Long = 10000
'Make sure we don't select cells hidden by filter
With Selection.SpecialCells(xlCellTypeVisible)
If Union(ActiveCell.EntireColumn, .EntireColumn).Address <> ActiveCell.EntireColumn.Address Then
MsgBox "Only select from one column"
Exit Sub
End If
If .Areas.Count = 1 And .Rows.Count = 1 Then
MsgBox "Select more than one cell"
Exit Sub
End If
If .Cells.Count > MAX_FILTER_CELLS Then
MsgBox "Cannot select more than " & MAX_FILTER_CELLS & " cells."
Exit Sub
End If
'Set the range to be filtered depending on whether it's a Table or not
If Not ActiveCell.ListObject Is Nothing Then
Set FilterRange = ActiveCell.ListObject.Range
InTable = True
Else
Set FilterRange = ActiveCell.CurrentRegion
End If
If Union(Selection, FilterRange).Address <> FilterRange.Address Then
MsgBox "Please make sure all cells are within the same table or contiguous area."
Exit Sub
End If
'If not in a table and we're filtering a different area than currently filtered
'then turn the existing AutoFilter off, so no error when the new area gets filtered.
If Not InTable And ActiveSheet.AutoFilterMode Then
If ActiveSheet.AutoFilter.Range.Address <> .CurrentRegion.Address Then
ActiveSheet.AutoFilterMode = False
End If
End If
'Try to add every selected value to a collection - only unique values will succeed
Set CollUniqueValues = New Collection
For i = 1 To .Areas.Count
For j = 1 To .Areas(i).Cells.Count
On Error Resume Next
CollUniqueValues.Add .Areas(i).Cells(j).Text, .Areas(i).Cells(j).Text
On Error GoTo 0
Next j
Next i
'Transfer the collection to an array for the AutoFilter function
ReDim Preserve FilterValues(1 To CollUniqueValues.Count)
For i = LBound(FilterValues) To UBound(FilterValues)
FilterValues(i) = CollUniqueValues(i)
Next i
'Determine the index of the column to be filtered within the FilterRange
ColNumberInFilterRange = (.Column - FilterRange.Columns(1).Column) + 1
FilterRange.AutoFilter Field:=ColNumberInFilterRange, Criteria1:=FilterValues, Operator:=xlFilterValues
End With
End Sub
How it Works
The first part of the code consists of a few checks to make sure the selection is more than one cell, is only in one column, and that you haven’t selected a huge number of cells. This last one is controlled by the MAX_FILTER_CELLS constant. I set it at 10,000, a number that ran with no noticeable delay.
After that the code checks whether the ActiveCell is in a table or not. In either case, it assigns the range to be filtered to the FilterRange variable. It then checks that all the currently selected cells reside within either the same table or contiguous area.
The code uses our old friend the “get unique items using a collection” trick. It then applies a filter to the FilterRange.
The trickiest part of the code was defining the conditions that require an Exit Sub, such as selecting in multiple columns. That’s mainly because something like “Selection.Columns.Count” only returns the count of columns in the first Area of a selection. The same thing applies to the Selection’s CurrentRegion. I got around these limitations by using Union and the fact that Selection.Address returns the address for all areas in the the selection.
Download
Here’s a workbook with the code and the pie orders table so you can easily try the filtering I did above.
