vba adding items to combobox from large list with multiples

[davewatson86]

Hello all

i am trying to add all the unique values from my range with the below code.

Private Sub UserForm_Initialize()
Dim sngLeft As Single
Dim sngTop As Single
Dim ColNum, i As Integer
Dim Name, myString As String
Dim strFound As Boolean


    Call ReturnPosition_CenterScreen(Me.Height, Me.Width, sngLeft, sngTop)
    Me.Left = sngLeft
    Me.Top = sngTop




    
    ColNum = 0
    
    While Not WS_Data_Dump.Range("K1").Offset(ColNum, 0).Value = ""
        Name = WS_Data_Dump.Range("K1").Offset(ColNum, 0).Value
    
        strFound = False
        With Me.ComboBox1
            'Loop through combobox
        
            For i = 0 To .ListCount - 1
                If .List(i) = myString Then
                    strFound = True
                    'Exit For
                End If
                C = C + 1
            Next i
            'Check if we should add item
            If Not strFound Then .AddItem (Name)
        End With
        ColNum = ColNum + 1
        
    Wend


        
End Sub

yet i seem to keep getting duplicate values once the user form has been initialized.

my code works through each used cell then checks if the value is in the combobox1.list if not it then ads it to the combobox1

where am i going wrong with my code and is this the most efficient way to do this?

i am at a loss so any help would be greatly appreciated.

regards
Dave

 

[Fluff]

How about

Private Sub UserForm_Initialize()
   Dim Cl As Range
   
   With CreateObject("scripting.dictionary")
      .CompareMode = 1
      For Each Cl In WS_Data_Dump.Range("K1", WS_Data_Dump.Range("K" & Rows.Count).End(xlUp))
         If Cl.Value <> "" Then .Item(Cl.Value) = Empty
      Next Cl
      Me.ComboBox1.List = .keys
   End With
End Sub

 

[davewatson86]

Many thanks for the reply Fluff

i actually managed to Make this work

Private Sub UserForm_Initialize()
Dim sngLeft As Single
Dim sngTop As Single


Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim rnData As Range
Dim vaData As Variant               'the list, stored in a variant
Dim ncData As New VBA.Collection    'the list, stored in a collection
Dim lnCount As Long                 'the count used in the On Error Resume Next loop.
Dim vaItem As Variant               'a variant representing the type of items in ncData


    Call ReturnPosition_CenterScreen(Me.Height, Me.Width, sngLeft, sngTop)
    Me.Left = sngLeft
    Me.Top = sngTop


    'Instantiate the Excel objects.
    Set wbBook = ThisWorkbook
    Set wsSheet = WS_Data_Dump


    'Using Sheet2,retrieve the range of the list in Column A.
    With wsSheet
        Set rnData = .Range(.Range("K1"), .Range("K30000").End(xlUp))
    End With


    'Place the list values into vaData.
    vaData = rnData.Value


    'Place the list values from vaData into the VBA.Collection.
    On Error Resume Next
        For lnCount = 1 To UBound(vaData)
        ncData.Add vaData(lnCount, 1), CStr(vaData(lnCount, 1))
    Next lnCount
    On Error GoTo 0
    
    'Clear the combo box (in case you ran the macro before),
    'and then add each unique variant item from ncData to the combo box.
    With Me.ComboBox1
        For Each vaItem In ncData
            .AddItem ncData(vaItem)
        Next vaItem
    End With




        
End Sub

 

[Fluff]

Glad you got it to work & thanks for the feedback.