excel vba dedupe array
'VBA function to dedupe one column in a 2d array.
'For example, the following puts the distinct values from column C
'on the worksheet into a variant array, b:
Dim a
a = DistinctVals([A1:F1000], 3)
'-------------------------------------------------------------------
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