adding distinct value in combobox...

[luca90]

Have a userforn in a vba for excel project
In column A (started from A2) have alist of value:
AAAAA
AAAAA
BBBBBB
CCCCC
CCCCC
CCCCC
....

how to fill combobox without dupes from column A?

[Siddharth Rout]

The answer lies in your thread which I answered in March 6th 2009

http://www.vbforums.com/showthread.php?t=560486

[anhn]

If your worksheet has 5000 items in column A, you should NOT add all of them to the combobox then search to remove 4900 duplicate items, ie. Do not add an item if it is already there.

This is one way to do it: (items added to combobox will also be sorted)

Private Sub UserForm_Initialize()
    Dim sItem As String
    Dim i As Long
    Dim r As Long
    
    '-- use CStr() to prevent cell contains error:
    '   #N/A    will be converted to "Error 2042",
    '   #DIV/0! will be converted to "Error 2007", ...
    With Me.ComboBox1
        r = 2
        sItem = CStr(Sheet1.Cells(r, 1))
        Do While Len(sItem) > 0
            For i = 0 To .ListCount - 1
                If sItem <= .List(i) Then Exit For
            Next
            If i >= .ListCount Then
                .AddItem sItem
            ElseIf sItem < .List(i) Then
                .AddItem sItem
            '-- if sItem = .List(i) then do not add it
            End If
            r = r + 1
            sItem = CStr(Sheet1.Cells(r, 1))
        Loop
    End With
End Sub

[Siddharth Rout]

Luca what is your data range? Remember time taken to sort data (In your Case) is totally dependent on the number of items that you want to sort and not on the time taken to add items to combobox...

Adding of items whether it is 5 items, 1000 items, 10000 items or 655356 items in your case will take less than 1 second!!!

[Null]

Luca what is your data range? Remember time taken to sort data (In your Case) is totally dependent on the number of items that you want to sort and not on the time taken to add items to combobox...

Adding of items whether it is 5 items, 1000 items, 10000 items or 655356 items in your case will take less than 1 second!!!

This is a stup.. advice that I have ever seen!
The time to sort on adding is much less than the time to search to remove duplicate items.
Wake up! The way the Oz guy uses is the best choice.

[luca90]

The answer lies in your thread which I answered in March 6th 2009

http://www.vbforums.com/showthread.php?t=560486

Wow!!!!!!!!!!!!
tks to remember me this post!
note:
But wth you are in your mind? A Gigabit of hardisk and a 3 Giga of memeory?

[Siddharth Rout]

Wow!!!!!!!!!!!!
tks to remember me this post!
note:
But wth you are in your mind? A Gigabit of hardisk and a 3 Giga of memeory?

Glad to be of help