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.

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.