excel distinct values code example

Example: excel vba get distinct values from range

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

'-----------------------------------------------------------------------

'If you happen to have Office365, the above function can be shortened
'to make use of the Office365 worksheet function 'UNIQUE()' instead 
'of the dictionary:

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