help: removing duplicative items in listbox

[jasonsweeney]

I have a listbox in a userform in which a large list of names is place
(using .additem).

What I want to do is this: After all the names are added into th
listbox, I want to remove any duplicative entries. I.e. if "Joh
Brown" is entered 3 times in the list, I want 2 of those entrie
removed from the list.

My guess is that I am making this much more painful than it has to be
but here is what I have (and it does not work):

__________________
Private Sub removeduplicates()

mainindex = 0
indexnumber = 0
totalcount = UserForm10.ListBox1.ListCount


For m = 0 To totalcount
UserForm10.ListBox1.ListIndex = mainindex
teststring = UserForm10.ListBox1.Value
totalcount = UserForm10.ListBox1.ListCount
UserForm10.ListBox1.ListIndex = indexnumber
MsgBox "comparing: " & teststring & " with: " & ListBox1.Value
If teststring = UserForm10.ListBox1.Value Then
MsgBox "Name already here: " & teststring
UserForm10.ListBox1.RemoveItem (indexnumber)
UserForm10.ListBox1.AddItem (teststring)
indexnumber = 0
End If
indexnumber = indexnumber + 1
If indexnumber >= totalcount Then
MsgBox "now finishing " & teststring
mainindex = mainindex + 1
indexnumber = 0
If mainindex >= totalcount Then
MsgBox "exiting sub"
Exit Sub
End If
MsgBox "total count is: " & totalcount
End If
Nex

 

[Tom Ogilvy]

John Walkenbach has sample code for doing this (he does it prior to loading
the listbox and sorts the entries to boot.

http://j-walk.com/ss/excel/tips/tip47.htm

 

[peter]

Hi,
Buy the book, Excel 2002 Power Programming with VBA by
John Walkenbach, page 424. I don't hink I'm allowed to
reprint his code. But if you get an answer from someone
else, the book is still worth it.

peter