How to delete duplicates in a listbox

[peter_z]

Hey Guys

Would really appreciate a code that can delete all the duplicates in VBA form list box.

My list box is called lbox_BDM.

If you have some code that can do this please share

Thanks!

 

[Marcelo Branco]

Hi,

Maybe this can help

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

M.

 

[peter_z]

Perfect looking through that it should do the trick nicely!

Thank you

 

[peter_z]

Can't seem to apply the code for the list box unfortunately...
I have made a dynamic form list box but one of the list boxes is creating duplicates.

Need something to call the list box and then delete the duplicates.

Hope that makes sense... Maybe something like this?

 Sub Sort_MasterAgent()
    Dim vaItems As Variant
    Dim i As Long, j As Long
    Dim vTemp As Variant
    
    'Put the items in a variant array
    vaItems = frm_MENU.lbox_MASTERAGENT.List     
 
For i = LBound(vaItems, 1) To UBound(vaItems, 1) - 1
    For j = i + 1 To UBound(vaItems, 1)
                If vaItems(j, 0) = vaItems(i, 0) Then
    
                End If
        Next j
    Next i

Cheers
Peter

 

[Marcelo Branco]

Hi Peter,

Assuming, as your last post:
UserForm name = frm_MENU
ListBox name = lbox_MASTERAGENT

maybe this (adapted from the link i've provided)

Sub aTest()
    Dim i As Long, j As Long
    Dim nodupes As New Collection
    Dim Swap1, Swap2, Item
    
    With frm_MENU.lbox_MASTERAGENT
    
        For i = 0 To .ListCount - 1
            ' 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
            nodupes.Add .List(i), CStr(.List(i))
        Next i
   
    '   Resume normal error handling
        On Error GoTo 0
    
        'Clear the listbox
        .Clear
        
        '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 and non-duplicated items to the ListBox
        For Each Item In nodupes
            .AddItem Item
        Next Item
        
    End With
'   Show the UserForm
    frm_MENU.Show
End Sub

HTH

M.

 

[peter_z]

Marcelo Branco, you are a genius!

Thanks a lot for your help

Much appreciated on a Friday afternoon!

 

[Marcelo Branco]

Thanks a lot for your help

You are welcome and tks for the feedback

M.

 

[Lebaron360]

This is awesome!! Nice work Marcelo!