Unique Numbers in ComboBox

[Rocky0201]

Hi...

I have a spreadsheet with over 300 rows. In column C I have a list of numbers that could range from 2 through 24. I wish to build my ComboBox in my userform with the list of unique numbers.

The following code works great building my ComboBox list however, it builds all of the numbers for all 300 plus rows even if there are numbers used multiple times.

Private Sub UserForm_Initialize()

With Sheets("Sheet1")
For Each c In .Range("C4", .Range("C" & Rows.Count).End(xlUp))
If c.Value <> "" Then ComboBox1.AddItem c.Value
Next c
End With

End Sub

Thanks for the help...

 

[GTO]

Presuming sorting is not an issue, the Dictionary is one way to quickly build a list of uniques.

Option Explicit
    
Private Sub UserForm_Initialize()
Dim DIC     As Object '<--- Dictionary
Dim aryVals As Variant
Dim i       As Long
    
    Set DIC = CreateObject("Scripting.Dictionary")
    
    With Sheet1 '<---CodeName OR tab name ---> ThisWorkbook.Worksheets("Sheet1")
        aryVals = Range(.Range("C4"), .Cells(.Rows.Count, "C").End(xlUp)).Value
    End With
    
    For i = LBound(aryVals, 1) To UBound(aryVals, 1)
        If Not aryVals(i, 1) = vbNullString Then DIC.Item(aryVals(i, 1)) = Empty
    Next
    
    Me.ComboBox1.List = DIC.Keys
End Sub

 

[jreedich]

Your code is running exactly right. You have not checked for what already exists in the combo box Once you put something into the combo box and sequence to the next cell you must now check if the new cell value is already in the list.

 

[Rocky0201]

Thanks GTO and jreedich!!!

GTO your expamle works great. To answer your comment regarding sorting, what if I would like to sort the list. Is this possible after I retrive all of the unique numbers?

Thank again...

 

[GTO]

As long as the vals are actual numbers, this would work for a smaller range (several hundred should run quick, but if you had thousands, the inefficiency of the sort would become obvious).

Option Explicit
        
Private Sub UserForm_Initialize()
Dim DIC     As Object
Dim aryVals As Variant
Dim i       As Long
Dim ii      As Long
Dim n       As Long
Dim bolAdded As Boolean
    Set DIC = CreateObject("Scripting.Dictionary")
    
    With Sheet1 '<---CodeName OR tab name ---> ThisWorkbook.Worksheets("Sheet1")
        aryVals = Range(.Range("C4"), .Cells(.Rows.Count, "C").End(xlUp)).Value
    End With
    
    For i = LBound(aryVals, 1) To UBound(aryVals, 1)
        If Not aryVals(i, 1) = vbNullString Then DIC.Item(aryVals(i, 1)) = Empty
    Next
    
    aryVals = DIC.Keys
    
    With Me.ComboBox1
        '// Plunk the first val from our array into the combobox.                       //
        .AddItem aryVals(LBound(aryVals))
        '// Loop thru the elements, and for ea element/val...                           //
        For ii = LBound(aryVals) + 1 To UBound(aryVals)
            '// reset                                                                   //
            bolAdded = False
            '// ...loop thru the list.                                                  //
            For i = 1 To .ListCount
                '// If the current element is less than the val in our current position //
                '// in the cbox's list, then insert the element/val in the same position,//
                '// 'pushing' the other vals in the list.                               //
                If CDbl(.List(i - 1, 0)) > aryVals(ii) Then
                    .AddItem aryVals(ii), i - 1
                    bolAdded = True
                    Exit For
                End If
            Next
            '// If we didn't find any vals in the .List greater than our current        //
            '// element/val, tack it in at the end of .List                             //
            If Not bolAdded Then
                .AddItem aryVals(ii)
            End If
        Next
    End With
End Sub

 

[Rocky0201]

Thanks GTO. Works Great!!

If I wanted to do the same thing for a column of names, would I use the same type of code but use strings instead of longs?

Thanks so much for your help...

 

[Rocky0201]

The answer, I take it is no... I tried to use the same code but received a Type Mismatch on the CDbl statement. Not knowing what CDbl does I assume it is testing numeric values. I watched the aryVals and the array was built with my names.

If there is another way to pick out the unique names and sort, that would be wonderful...

Thanks again GTO

 

[GTO]

Thanks GTO. Works Great!!

If I wanted to do the same thing for a column of names, would I use the same type of code but use strings instead of longs?

The answer, I take it is no... I tried to use the same code but received a Type Mismatch on the CDbl statement. Not knowing what CDbl does I assume it is testing numeric values. I watched the aryVals and the array was built with my names.

If there is another way to pick out the unique names and sort, that would be wonderful...

Thanks again GTO

You are most welcome.

If you want to sort strings, then rather than coerce the return from the combobox's .list, you can coerce the return from the array.

                If .List(i - 1, 0) > CStr(aryVals(ii)) Then

BTW, if you select CDbl() or CStr() in the code and press the F1 key, you should be taken to the Help topic for both, as they are both Type Conversion Functions. In short, any textbox, listbox, combobox returns string values (regardless of whether you plunked in numbers), so I had the CDbl() to coerce the value to a Double, so that we were comparing numbers in the array (a Variant Array will sub-type the elements to numbers or string as appropriate) to numbers (from the list) in order to sort correctly.

 

[Rocky0201]

Thank you GTO... You have been most helpful

 

[GTO]

You are most welcome; glad that helped