Quicker way to get all unique values of a column in VBA?

user_2245304 0
Viewed 212749

Is there a faster way to do this?

Set data = ws.UsedRange

Set unique = CreateObject("Scripting.Dictionary")

On Error Resume Next
For x = 1 To data.Rows.Count
    unique.Add data(x, some_column_number).Value, 1
Next x
On Error GoTo 0

At this point unique.keys gets what I need, but the loop itself seems to be very slow for files that have tens of thousands of records (whereas this wouldn't be a problem at all in a language like Python or C++ especially).

vba excel
5 Answers

it's funny because i've had to read these instructions over and over again, but it think i worked out a much faster way to do this:

Set data = ws.UsedRange
dim unique as variant
unique = WorksheetFunction.Unique(data)

And then you can do whatever you want with the unique array such as iterating it:

For i = LBound(unique) To UBound(unique)
    Range("Q" & i) = indexes(i, 1)
Next
user_156090350
Related