VBA: Removing duplicates from a Collection [duplic

Question

This question already has an answer here:

• vba: get unique values from array 9 answers

What I am trying to accomplish

I'd like to take a series of values from a selection of cells, then fill a new cell which contains only unique values from the selection I made. here is my code so far:

Const Delimiter = ", "
Dim num As Range
Dim a As Variant
Dim Concat, bucket As New Collection

#to create a collection that contains all the values from my selection 
For Each num In Selection
  a = Split(num, Delimiter)
  Concat.Add (a)
Next num

#to convert multidimensional collection to a single dimensional
For i = 1 To Concat.Count
  For j = 1 To Concat(i).Count
    bucket.add(Concat(i)(j))
  Next i
Next j

#to remove duplicate values
[code]

#to output to excel
[code]

As you can see, the code is incomplete. I am having issues with the following lines of code

For j = 1 To Concat(i).Count

I get a "Run-time error '424': Object required" error.

Answer 1

Using a dictionary will make it a lot simpler and easier. See below.

Sub UniqueValues()
    Const Delimiter = ","
    Dim num As Range
    Dim a As Variant
    Dim i As Integer, j As Integer
    Dim dctData As New Dictionary

    'Loop through all the values in the cells (including splitting into arrays)
    For Each num In Selection
        a = Split(num, Delimiter)
        For j = 0 To UBound(a, 1)
            If Not dctData.Exists(Trim(a(j))) Then
                dctData.Add Trim(a(j)), ""
            End If
        Next j
    Next num

    '#to output to excel
    For Each a In dctData
        Debug.Print a
    Next a
End Sub