Distinct Data In Combo Box

  • Thread starter Thread starter mattis2k
  • Start date Start date
M

mattis2k

Hi,

I have a range of data that i need to remain intact, i would like this
data to be presented in a combo box also...below is example data

A1
1
1
1
2
2
3
4
4

what i need is for the combo box to only list 1234, not 11122344..

Can anyone help ?

matt
 
Hi Matt

Here's a very quick and extremely dirty macro solution for range A1:A300 and Combobox1 in
Sheet1:

Sub Test()
Dim Coll As New Collection
Dim C As Range
Dim X As Variant
On Error Resume Next
For Each C In Range("A1:A300")
X = C.Text
If X <> "" Then Coll.Add X, X
Next
Sheets(1).ComboBox1.Clear
For Each X In Coll
Sheets(1).ComboBox1.AddItem X
Next
Set Coll = Nothing
End Sub
 
If we were talking about 30,000 rows I'd dispute the 'very quick'
claim <g>. This slight amendment will give better performace:

Dim Coll As New Collection
Dim C As Range
Dim X As Variant
Sheets(1).ComboBox1.Clear
On Error Resume Next
For Each C In Range("A1:A300")
X = C.Text
If Len(X) = 0 Then
Coll.Add X, X
If Err.Number = 0 Then
Sheets(1).ComboBox1.AddItem
Else
Err.Clear
End If
End If
Next
On Error GoTo 0
Set Coll = Nothing
 
....and the better performance isn't due to me getting this line completely wrong:

If Len(X) = 0 Then

of course should be

If Len(X) > 0 Then

Oops!
 
onedaywhen said:
If we were talking about 30,000 rows I'd dispute the 'very quick'
claim <g>.
Click to expand...

True. It was "quick" as in "fast written".
This slight amendment will give better performace:
Click to expand...

Also true. That last For Each part was to demonstrate how to loop a
collection (for sorting purposes or whatever). Worth mentioning is that
collections are very quick and efficient, but they can be very memory
hungry.

Best wishes Harald
Followup to newsgroup only please
 
Back
Top