remove duplicates in listbox

B

bcorbin

:( :confused:
Sorry if this is a stupid problem but I am very new to VBA. I a
writing a VBA app in a CAD program and I have managed to get
multi-column listbox which is exactly what I want, except for th
duplicate entrys (see attached image).
Is there any way to now get rid of the duplicates (entire row) from th
listbox?

Thanks for any help anyone can provide, as I'm really lost.... ;-)

Bria

Attachment filename: listbox.jpg
Download attachment: http://www.excelforum.com/attachment.php?postid=55050
 
C

cmdecker2

Here:
subSetupform()
For Each c In frmAddEntry.Controls
If Left(c.Name, 2) = "cb" Then
c.List = CreateList(YourRangeAddress)
End If
Next c
End Sub


Function CreateList(myRange)
Dim myControl
Dim mystring As String
Dim Cell As Range
Dim NoDupes As New Collection
Dim i As Integer
Dim j As Integer
Dim Swap1, Swap2, Item
Dim cbList() As Variant

' The next statement ignores the error caused by attempting to add
' a duplicate key to the collection.
' The duplicate is not added - which is just what we want!
On Error Resume Next
For Each Cell In Range(myRange)
If Cell.Value <> "" Then NoDupes.Add Cell.Value, Cell.Value
' Note: the 2nd argument (key) for the Add method must be a
string
Next Cell

On Error GoTo 0

' Sort the collection (optional)
For i = 1 To NoDupes.Count - 1
For j = i + 1 To NoDupes.Count
If NoDupes(i) > NoDupes(j) Then
Swap1 = NoDupes(i)
Swap2 = NoDupes(j)
NoDupes.Add Swap1, before:=j
NoDupes.Add Swap2, before:=i
NoDupes.Remove i + 1
NoDupes.Remove j + 1
End If
Next j
Next i

' Add the sorted, non-duplicated items to a ComboBox
ReDim cbList(NoDupes.Count) 'reset to same number as in no dupes
j = 0
For Each Item In NoDupes
j = j + 1
If Item <> "" Then cbList(j) = Item
Next Item

For j = 1 To NoDupes.Count ' Remove names from the collection.
NoDupes.Remove 1 ' removes the current first member on each
iteration.
Next

CreateList = cbList()
End Function

Now go buy a Power Programming book by :John Walkenbach
BEST investment you'll ever make!

Carl
 
T

Tom Ogilvy

You can also see the original version of the code at John Walkenbach's site:

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

although Carl has altered it slightly to make it a function.

--
Regards,
Tom Ogilvy


cmdecker2 said:
Here:
subSetupform()
For Each c In frmAddEntry.Controls
If Left(c.Name, 2) = "cb" Then
c.List = CreateList(YourRangeAddress)
End If
Next c
End Sub


Function CreateList(myRange)
Dim myControl
Dim mystring As String
Dim Cell As Range
Dim NoDupes As New Collection
Dim i As Integer
Dim j As Integer
Dim Swap1, Swap2, Item
Dim cbList() As Variant

' The next statement ignores the error caused by attempting to add
' a duplicate key to the collection.
' The duplicate is not added - which is just what we want!
On Error Resume Next
For Each Cell In Range(myRange)
If Cell.Value <> "" Then NoDupes.Add Cell.Value, Cell.Value
' Note: the 2nd argument (key) for the Add method must be a
string
Next Cell

On Error GoTo 0

' Sort the collection (optional)
For i = 1 To NoDupes.Count - 1
For j = i + 1 To NoDupes.Count
If NoDupes(i) > NoDupes(j) Then
Swap1 = NoDupes(i)
Swap2 = NoDupes(j)
NoDupes.Add Swap1, before:=j
NoDupes.Add Swap2, before:=i
NoDupes.Remove i + 1
NoDupes.Remove j + 1
End If
Next j
Next i

' Add the sorted, non-duplicated items to a ComboBox
ReDim cbList(NoDupes.Count) 'reset to same number as in no dupes
j = 0
For Each Item In NoDupes
j = j + 1
If Item <> "" Then cbList(j) = Item
Next Item

For j = 1 To NoDupes.Count ' Remove names from the collection.
NoDupes.Remove 1 ' removes the current first member on each
iteration.
Next

CreateList = cbList()
End Function

Now go buy a Power Programming book by :John Walkenbach
BEST investment you'll ever make!

Carl
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top