Thread: Delete duplicate entries in a list

This seems to be an often asked question but I am not finding an answer that really helps. I have a list which has a bunch of entries which duplicate many times. What I want to do is to list all the unique values in a combobox. I have the list into the combobox using:

cbo.list=sheet(1).range(C3:C500).value

No fuss. Now I want to go through the list and remove the duplicates. But how?

I do not want to do it on the sheet... I know I can use worksheet functions and transplant the unique values in my first range, to another location on the sheet and then use that as the source for my list. To me, that is messy. So is the above possible? Am I going the wrong way and do I want to cycle through my range and add values unless they equal a value already existing in the list?

Many thanks for your time

Adam

Try this:-

Private Sub UserForm_Initialize()
Dim Rng As Range, Dn As Range
Set Rng = Range(Range("C1"), Range("C" & rows.Count).End(xlUp))
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
    For Each Dn In Rng
        .Item(Dn.value) = Dn.value
    Next
Me.ComboBox1.List = Application.Transpose(.keys)
End With
End Sub

Regards Mick

Thanks Mick,

That is pretty much the same as I came up with but with a few little extras that make it better (I forgot to get it to just go to the row count).

Thanks again