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?
Printable View
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?
The answer lies in your thread which I answered in March 6th 2009 ;)
http://www.vbforums.com/showthread.php?t=560486
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)
Code: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
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!Quote:
Originally Posted by koolsid
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.
Wow!!!!!!!!!!!!Quote:
Originally Posted by koolsid
tks to remember me this post!
note:
But wth you are in your mind? A Gigabit of hardisk and a 3 Giga of memeory?:D:D:D:thumb:
Glad to be of help ;)Quote:
Originally Posted by luca90