Trouble populating multi column ActiveX ListBox with unique values from 2 dimensional range (VBA)

[Legacy 332279]

Hey,

I'm trying to populate a ActiveX ListBox through VBA with unique values from a two dimensional range, but I can't seem to find a way to both only get unique, sorted values and populate two columns with those values. That is to say, I have no issues with populating a single column in the listbox with unique, sorted values or populating two columns without omitting duplicate values, but I don't get how to do both.

So, in essence, what i would like to accomplish is to populate the ListBox with unique, sorted values from a two dimensional range (for example "A1:B10") into two columns. To clarify, the sum of both columns in each row should be unique so that, as an example, both row 1 and 2 are added to the listbox, but not row 3, based on the example range below:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]C[/TD]
[/TR]
</tbody>[/TABLE]

Currently I'm working on the basis of the following script, which populates the listbox with a single column of unique, sorted values:

Sub Update_ListBox1()

L_ID = Sheets("ID-Data").Range("a1:a100")

With CreateObject("System.Collections.ArrayList")[INDENT]For Each cl In L_ID[/INDENT]
[INDENT=2]If cl <> "" And Not .contains(cl) Then .Add cl[/INDENT]
[INDENT]Next[/INDENT]
[INDENT].Sort[/INDENT]
[INDENT]ListBox1.List = Application.Transpose(.toarray())[/INDENT]
End With


End Sub

Any ideas on how I could add B1:B100 to the second column of the listbox while still keeping the values unique and sorted as described above?

 

[Takae]

Just unique...hope this helps

Sub Update_ListBox1()
 Dim Dic, i As Long, buf As String, Keys, x()
    Set Dic = CreateObject("Scripting.Dictionary")
    On Error Resume Next
    For i = 2 To 10
        buf = Cells(i, 1).Value & "," & Cells(i, 2).Value
        Dic.Add buf, buf
    Next i
    
    Keys = Dic.Keys
        
        For i = 0 To Dic.Count - 1
        ReDim Preserve x(i)
           x(i) = Replace(Keys(i), ",", "")
         Next i
        ListBox1.List = x
    Set Dic = Nothing
End Sub

 

[Legacy 332279]

Thanks, but unfortunaley I do need the values to be populated across two columns in the listbox in order to get the selected item's values from the columns seperately. I think I can get the values sorted another way, so if the list turns out sorted or not isn't that important at this stage, but getting the unique values populated across two columns is.