Distinct Data In Combo Box
 

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


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Distinct Data In Combo Box
 

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

--
HTH. Best wishes Harald
Followup to newsgroup only please.

"mattis2k" wrote in message
...

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


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Distinct Data In Combo Box
 

See John Walkenbach's site for sample code:

http://j-walk.com/ss/excel/tips/tip47.htm
Filling a ListBox With Unique Items

--
Regards,
Tom Ogilvy


"mattis2k" wrote in message
...

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


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Distinct Data In Combo Box
 

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


"Harald Staff" wrote in message ...
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

--
HTH. Best wishes Harald
Followup to newsgroup only please.

"mattis2k" wrote in message
...

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


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Distinct Data In Combo Box
 

....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!

Distinct Data In Combo Box
 

"onedaywhen" skrev i melding
om...
If we were talking about 30,000 rows I'd dispute the 'very quick'
claim <g.

True. It was "quick" as in "fast written".

This slight amendment will give better performace:

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