How to Extract Unique Values from ANYTHING in Excel with VBA
Collect uniques (or cities) like Ron Burgundy
How many times have you heard this old chestnut: “Can you tell me how many unique Xs there are?”
A shitload of times, that’s how many.
Maybe you’ve had success using a formula or a Pivot Table, but the problem gets a whole lot more interesting when you’re stuck with more than one column of data. Let’s fix that with a VBA 1-2 punch:
- Use the built-in Collection object to store unique values from anything
- Write the results to a new sheet named “uniques-only”
First things first — here’s how to wrap a function around VBA’s Collection and get it to store all uniques.
Let’s review this first step using our 4-step VBA process as a guide:
Step 1 – Setup
Step 2 – Exploration
Step 3 – Execution
Step 4 – Cleanup
Interestingly, with this particular function, there is no setup beyond declaring variables. Nice! With that, Step 1 – Setup is complete.
Step 2 – Exploration is very limited in this particular function as well, essentially boils down to a 2-part guard clause from lines 6-11. Let’s talk about both of the cases we’re checking for on line 8.
First, in the case that a Nothing Range is passed in, we want to return a Nothing Collection. What is a Nothing Range, you ask? Good question.
Suppose you declared a Range variable but never assigned it to anything <- THAT is a Nothing Range. Nothing typically occurs when VBA knows about a variable (i.e. Dim rngMyTargetRange As Range), but the variable is never assigned.
We want this function to bulletproof, so lines 8-11 check the passed-in rng to see if it is Nothing. If so, we set the output of the function to col, which has been declared but not assigned to anything (i.e. Nothing) and exit the function.
Second, in the case that a real Range is passed in, but it’s full of blank cells, we also want to return a Nothing Collection. After all, we don’t really care about empty cells — they’re not really unique!
We check the entire rng for “emptiness” by examining the output of WorksheetFunction.CountA(rng), which will be 0 if every cell empty. If that’s the case, we assign the function output to the Nothing col variable and exit, just like we would if rng was Nothing.
Step 3 – Execution is the big one here, and takes place from lines 13 to 35. There’s a lot going on in these ~20 lines, so let’s take it nice and slow.
On line 13, we check to see if the passed-in Range is a single cell. If that’s the case, the returned Collection will be that single value only, so we bind col on line 14 by assigning it to a New Collection and add a single Item to col on line 15. Boom!
When the passed-in Range covers more than one cell (i.e. the vast majority of cases), we get our work done on lines 19-34.
Line 19 assigns the passed-in Range to a Variant array for speedy looping. Line 20 binds col to a New Collection, preparing it to eventually be populated.
On line 24, we tell VBA to ignore errors with On Error Resume Next. Usually, this is to be avoided — but it’s actually critical to this function! We are eventually going to be adding key / value pairs to a Collection in this function. VBA will not add duplicate keys to a Collection, and attempting to do so results in an error — so, by ignoring the error, we’re effectively skipping duplicates!
On line 28, we kick off a loop through everything in the Variant array we created above (on line 19). Since we’re looking for uniques and do not care about the order in which they are collected, the For Each X in Y syntax works beautifully — in each iteration, the variable var contains an element of the Variant array. Smooth!
Finally, our Step 4 – Cleanup takes place on lines 34-38. Line 34 flips errors back on (since we have finished looping through the Variant array), and line 38 ensures that the function returns the uniques-only Collection.
OK awesome — we have a Function that returns a Collection of uniques… it’s time to actually do something with it!
10 codes, 2 repeats, 8 uniques total
If we want to get a single-column list of the uniques across both columns there, the code below will do the trick.
Dust off that 4-step VBA process y’all, let’s get into it:
Step 1 – Setup
Step 2 – Exploration
Step 3 – Execution
Step 4 – Cleanup
This one is kind of neat in that it goes straight to the user for Step 1 – Setup and Step 2 – Exploration. On line 12 we assign the message we’re going to prompt the user with, and on line 14 we use Application.InputBox (with Type:=8 to allow for a Range selection) to set the target.
You might be asking yourself, “Why is this wrapped in another On Error Resume Next block?”, and like 2Pac I ain’t mad at cha.
“Why do we need an On Error Resume Next block?”
The block allows the user to click “Cancel” error-free and is resolved nicely on line 16, where we check to see if rngTarget is Nothing. (If so, we exit the subroutine.)
The Step 3 – Execution phase is where the magic really happens, and we start on line 19 by using the CollectUniques function we just wrote. This one-liner gives us colUniques, a Collection of all the unique codes that the user selected.
With that, we COULD simply loop through colUniques and write each value out to a new Worksheet. When it comes to writing values to Worksheets, though, I recommend using a Variant array for the performance benefits (since they translate really nicely into Ranges). This example is tiny, but once you have many values the speed will be really noticeable.
On lines 23-26, we fill up a Variant array to make the write process lightning fast. Let’s go line by line here, because there’s a lot going on:
- Line 23: we use ReDim to size the Variant array, varUniques, to be a single column that is exactly as long as the Collection of uniques
- Line 24: we loop from 1 to the number of uniques in the Collection
- Line 25: Variant arrays, interestingly enough, are zero indexed, so we use the index variable (lngIdx) minus one. That zero index applies to the column dimension too, so we’re writing to column number 0!
Phew! In lines 29-31, we add a new Worksheet and take advantage of the fact that Variant arrays can be set to Range for a SUPER fast write. We accomplish this by sizing rngUniques (which will eventually be set to the Variant array from above) along column A, ensuring it is exactly as long as the total number of uniques.
At that point, getting all the values out is as easy as line 31!
Finally, our Step 4 – Cleanup is simply a MsgBox to the user to let him or her know the script has finished. And with that, you’ve got an easy-to-reference, columnar list of uniques!
Want to see the script in action? Check out this 7-minute walk through:
Are you banging out uniques without breaking a sweat? If not, let me know and I’ll help you get what you need! And if you’d like more step-by-step, no-bullshit VBA guides delivered direct to your inbox, join my email newsletter below.