stoping duplication in drop down list
 

Hello
Im using a simple loop to check part od a column an add thecontents of its
cells to a dropdown list. The code is below

rowno = 9
Do Until IsEmpty(ActiveSheet.Cells(rowno, 2).Value)
Me.cmbHolding.AddItem ActiveSheet.Cells(rowno, 2).Value
rowno = rowno + 1
Loop

Problem is the items from the column can occur more than one, but I would
like to include them in the dropdown list only once. Is there a commonly
used solution for this as I imagine it is a common problem.

Thanks
Ian

stoping duplication in drop down list
 

John Walkenbach shows how:
http://j-walk.com/ss/excel/tips/tip47.htm

It's actually for a listbox, but you'll see how he does it.

mantrid wrote:

Hello
Im using a simple loop to check part od a column an add thecontents of its
cells to a dropdown list. The code is below

rowno = 9
Do Until IsEmpty(ActiveSheet.Cells(rowno, 2).Value)
Me.cmbHolding.AddItem ActiveSheet.Cells(rowno, 2).Value
rowno = rowno + 1
Loop

Problem is the items from the column can occur more than one, but I would
like to include them in the dropdown list only once. Is there a commonly
used solution for this as I imagine it is a common problem.

Thanks
Ian

--

Dave Peterson

stoping duplication in drop down list
 

Thanks Dave
That did it
Ian
"Dave Peterson" wrote in message
...
John Walkenbach shows how:
http://j-walk.com/ss/excel/tips/tip47.htm

It's actually for a listbox, but you'll see how he does it.

mantrid wrote:

Hello
Im using a simple loop to check part od a column an add thecontents of
its
cells to a dropdown list. The code is below

rowno = 9
Do Until IsEmpty(ActiveSheet.Cells(rowno, 2).Value)
Me.cmbHolding.AddItem ActiveSheet.Cells(rowno, 2).Value
rowno = rowno + 1
Loop

Problem is the items from the column can occur more than one, but I
would
like to include them in the dropdown list only once. Is there a commonly
used solution for this as I imagine it is a common problem.

Thanks
Ian

--

Dave Peterson

stoping duplication in drop down list
 

An alternative, where delim is a character (used as delimeter) not found in
the cell range:

Dim arr As Variant
arr = UniquesOnly(Range("A1:A100"), ";")
Me.ListBox1.List = arr


Function UniquesOnly(r As Range, delim As String) As Variant
Dim txt As String
Dim c As Range

txt = delim
For Each c In r.Cells
If Len(c.Value) 0 Then
If InStr(txt, delim & c.Value & delim) = 0 Then
txt = txt & c.Value & delim
End If
End If
Next
txt = Mid$(txt, 2, Len(txt) - 2)
UniquesOnly = Split(txt, ";")
End Function

Regards,
Greg