Remove duplicates in Listbox

[Bruno74]

Hello to all.

I know that there are lots of info on this but i can´t put it to work, so i ask for help

I created a Lisbox1 that populates when i click the search button.
Have a Dynamic range for populating ( Workbook \ Formulas \ Name Manager - =OFFSET(Sheet1!$A$2;0;0;COUNTA(Sheet1!$A:$A)-1;10) )
And this code in the button: ( ListBox1.RowSource = "test" )


It populates with 10 lines. 5 lines the name Mike and 5 lines the name John.
I would like to populate with (in this case) 2 lines. One for Mike and other for John.
I would like do populate only with unique names. Remove the duplicates.

I search and found lots of code for this, but it doesn´t seem to work for me.
Am i putting the code in the wrong place? Do i have to change something?


Example of code i found for removing duplicates:




Sub Sample()
RemovelstDuplicates ctrlListNames
End Sub




Public Sub RemovelstDuplicates(lst As msforms.ListBox)
Dim i As Long, j As Long
With lst
For i = 0 To .ListCount - 1
For j = .ListCount - 1 To (i + 1) Step -1
If .List(j) = .List(i) Then
.RemoveItem j
End If
Next
Next
End With
End Sub


Tks!

 

[dmt32]

Hi,
try this change to your codes

Place in Userform code Page:

Private Sub CommandButton1_Click()
    RemovelstDuplicates Me.ListBox1
End Sub


Private Sub UserForm_Initialize()
    With Me.ListBox1
        .RowSource = ""
        .List = Range("Test").Value
    End With
End Sub

Change Name of Listbox shown in red as required

It is assumed you have placed RemoveIstDuplicates procedure in standard module.



Dave

 

[Bruno74]

Hello Dave.

No, i don´t have nothing placed in Module.
How can i place a RemoveIstDuplicates procedure in standard module ?
Sorry, it´s my first project

And tks for the help!

 

[dmt32]

From VBA Editor > Insert > Module

Placing code in standard module makes it available to other userforms if needed.

The other codes go in your userforms code page (just double click the form)

Dave

 

[Bruno74]

I must be doing something wrong. No errors but still duplicates

I have placed this code you gave me like this
Module:
Private Sub MyButton1_Click()
RemovelstDuplicates Me.ListBox1
End Sub

And placed this code in the Form. If i double click the Form it open as (Private Sub UserForm_Activate instead of Initialize)
Private Sub UserForm_Initialize()
With Me.ListBox1
.RowSource = ""
.List = Range("Test").Value
End With
End Sub

 

[dmt32]

Just did test & worked ok for me

Is MyButton1_Click a commandbutton of your userform?

You can delete the UserForm_Activate code tag.

Dave

 

[Bruno74]

i can´t get it to work

MyButton is a command button in my Userform. I have a TextBox to input search and the button activates it.
It works and populates an ListBox, but with duplicates

I did this:
--> copied the code "RemovelstDuplicates" to Module
--> copied the MyButton1_Click to module
--> copied the UserForm_Initialize() code to Userform

 

[dmt32]

MyButton1_Click should be on your Userforms code page

If you are still having problems place your workbook in a dropbox (public folder) and provide a link to it here.
I or others here should be able to guide you further

Dave

 

[mikerickson]

I'm assuming that the code that loads your userform's list box is in the userforms Intialize event.
The remove duplicates code should be called after that has run.
The code to remove the duplicates should be in the userform's code module. (To my way of thinking, if the code only works on a userform, it belongs in that userform's code module).

I just noticed that you are using RowSource to populate your listbox, RemoveItem doesn't work when using RowSource.

Try this in the Userform's code module. (first remove any default values for RowSource if you are using them)

' in userform code module

Private Sub UserForm_Initialize()
    ListBox1.List = Range("DynamicNamedRange").Value
    RemoveDuplicates ListBox1
End Sub

Sub RemoveDuplicates(aListBox As MSForms.ListBox)
    Dim i As Long, j As Long
    With aListBox
        For i = .ListCount - 1 To 1 Step -1
            For j = 0 To i - 1
                If (.List(i, 0) = .List(j, 0)) Or (.List(i, 0) = vbNullString) Then
                    .RemoveItem i
                    Exit For
                End If
            Next j
        Next i
    End With
End Sub

and invoke the userform with code like this in a normal module

Sub ShowUF()
    Userform1.Show
End Sub

 

[Bruno74]

Tks Dave for your time.

Mike:
The code that loads my Listbox1 is in Button1, that searches for a value that i´ve puted on Textbox1
Code at the end of button1: ListBox1.RowSource = "Test"

Nothing works.... i tried putting your code in the Module, in the form, in the button but nothing works...still duplicates in Listbox1