excel vba dedupe array code example

Example: 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