Excel VBA: Get Unique Values Easily

Photo by Alexander Sinn on Unsplash

On December 3, 2019, the Office 365 Monthly Channel version of Excel offered some pretty powerful new functions. One of them is UNIQUE(), a long overdue function indeed.

But if you don’t yet have the latest and greatest Excel subscription, you can still easily get distinct values from a range of cells by using VBA. And this works in any PC version of Excel.

There are many ways to get distinct values from a worksheet column when working in VBA. Using the Advanced Filter or rolling your own function are reasonable options.

But using the scripting dictionary is fast and easy:

Function DistinctVals(a, Optional col = 1)
    Dim i&, v: v = a
    With CreateObject("Scripting.Dictionary")
        For i = 1 To UBound(v): .Item(v(i, col)) = 1: Next
        DistinctVals = Application.Transpose(.Keys)
    End With
End Function

And that’s it. Really!

But you should also strongly consider a reusable routine to write an array to a range:

Sub SetRange(r As Range, vArr)
    r.Resize(UBound(vArr, 1), UBound(vArr, 2)) = vArr
End Sub

Now you can get a distinct list of values from a column and write the data where you want as simply as:

SetRange [c1], DistinctVals([a1:b1000], 2)

Notice we passed in a range of two columns and we selected to get the distinct values from the second column.

If you happen to have Office 365 Monthly Channel, this VBA function can be shortened to make use of the Office 365 worksheet function, UNIQUE(), instead of the dictionary:

Function DistinctVals(r As Range)
  DistinctVals = WorksheetFunction.Unique(r)
End Function

Let me know your thoughts in the comments…