excel vba get unique values from array
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