Populate a listbox from a range skipping duplicates

[TPFKAS]

My spreadsheet contains a range of cells with dates. They are in random order and there are duplicates
I want to use this range in a listbox on a userform. But I want to prevent it showing duplicates and preferably would have the dates ordered from old to new.
I am trying to do this by copying the data elsewhere, sorting and then removing duplicates, but I guess there must be a more elegant way to do this.

Anybody any suggestions?

 

[Akuini]

Try this:
Let's say the data start at A2 in "sheet1".

[FONT=lucida console][COLOR=Royalblue]Private[/COLOR] [COLOR=Royalblue]Sub[/COLOR] UserForm_Initialize()
    [COLOR=Royalblue]Call[/COLOR] toList
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR]

[COLOR=Royalblue]Sub[/COLOR] toList()
[COLOR=Royalblue]Dim[/COLOR] i [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
[COLOR=Royalblue]Dim[/COLOR] vList
[COLOR=Royalblue]Dim[/COLOR] dar [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Object[/COLOR]

[COLOR=Royalblue]With[/COLOR] Sheets([COLOR=brown]"Sheet1"[/COLOR])
vList = .Range([COLOR=brown]"A2"[/COLOR], .Cells(.Rows.Count, [COLOR=brown]"A"[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp))
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]With[/COLOR]
    
    [COLOR=Royalblue]Set[/COLOR] dar = CreateObject([COLOR=brown]"System.Collections.ArrayList"[/COLOR])
    
    [COLOR=Royalblue]For[/COLOR] i = LBound(vList) [COLOR=Royalblue]To[/COLOR] UBound(vList)

            [COLOR=Royalblue]If[/COLOR] [COLOR=Royalblue]Not[/COLOR] dar.Contains(vList(i, [COLOR=crimson]1[/COLOR])) [COLOR=Royalblue]And[/COLOR] vList(i, [COLOR=crimson]1[/COLOR]) <> [COLOR=brown]""[/COLOR] [COLOR=Royalblue]Then[/COLOR]
                dar.Add vList(i, [COLOR=crimson]1[/COLOR])
            [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
            
    [COLOR=Royalblue]Next[/COLOR]
       
       dar.Sort
       ListBox1.List = dar.Toarray()

[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

 

[DanteAmor]

Try this,
Change D for the column with the date

Private Sub UserForm_Activate()
  Dim arrList As Object, dict As Object, a As Variant, i As Long, ky As Variant
  Set dict = CreateObject("Scripting.Dictionary")
  Set arrList = CreateObject("System.Collections.ArrayList")
[COLOR=#008000]  'unique values[/COLOR]
  a = Range("[COLOR=#ff0000]D[/COLOR]2", Range("[COLOR=#ff0000]D[/COLOR]" & Rows.Count).End(xlUp))
  For i = 1 To UBound(a)
    dict(a(i, 1)) = dict(a(i, 1))
  Next
[COLOR=#008000]  'sort values[/COLOR]
  For Each ky In dict.keys
    arrList.Add ky
  Next
  arrList.Sort
[COLOR=#008000]  'Populate listbox[/COLOR]
  ListBox1.List = arrList.toArray
End Sub

 

[TPFKAS]

Hi Akuini & Dante,
Both of your scripts work as a charm. Thanks!
Still a lot to learn for me....
Cheers.

 

[Akuini]

You're welcome, glad to help, & thanks for the feedback.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.