Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
distinct count summarize Data for pivoit table | Excel Discussion (Misc queries) | |||
distinct | Excel Worksheet Functions | |||
Extracting distinct data | Excel Discussion (Misc queries) | |||
Can one combo box control the data in a different combo box | Excel Discussion (Misc queries) | |||
Distinct | Excel Worksheet Functions |