stoping duplication in drop down list

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mantrid - 26 Apr 2007 18:39 GMT

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

Reply to this Message

Dave Peterson - 26 Apr 2007 19:37 GMT

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.

> Hello
> Im using a simple loop to check part od a column an add thecontents of its
[quoted text clipped - 12 lines]
> Thanks
> Ian

Signature

Dave Peterson

Reply to this Message

mantrid - 26 Apr 2007 21:48 GMT

Thanks Dave
That did it
Ian

> John Walkenbach shows how:
> http://j-walk.com/ss/excel/tips/tip47.htm
[quoted text clipped - 17 lines]
> > Thanks
> > Ian

Reply to this Message

Greg Wilson - 27 Apr 2007 08:24 GMT

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

Reply to this Message