Filter Column By All Selected Values

Filter Column By All Selected Values

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.

Filter by All Selected Cells

Here’s the result:

filtered ccells

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…

=COUNTIFS([Name],[@Name],[Pie],"Red velvet cheesecake")>0

and then filter to all the rows that return TRUE.

COUNTIFS filter

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.

Filter Step 1

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.

Filter Step 2

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.

Sub Filter_By_All_Selected_Values()

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.

8 thoughts on “Filter Column By All Selected Values

  1. Neato! I was going to write some code like this and put it in the book. Now I don’t have to: I’ll just link to your evil genius routine instead. Thanks Doug.

  2. It strikes me the Advanced Filter and ‘Filter in place’ option would be perfect for filtering a large table quickly based on an external range. Just needs a “Where are your filter terms” option, to give the external terms the named range Criterai.

    • I think that’s true. For some reason I never use Advanced Filter. It’s also seldom part of a finished product for me. I’m usually just filtering to try to figure out what’s going on with a dataset.

  3. Pingback: Excel Roundup 20150511 « Contextures Blog

Leave a Reply to Jeff Weir Cancel reply

Your email address will not be published. Required fields are marked *

To post code, do this: <code> your vba here </code>