Remove duplicates in Listbox

Bruno74

New Member
Joined
Jan 25, 2019
Messages
14
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!
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Hi,
try this change to your codes

Place in Userform code Page:

Rich (BB code):
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
 
Last edited:
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Just did test & worked ok for me

Is MyButton1_Click a commandbutton of your userform?

You can delete the UserForm_Activate code tag.

Dave
 
Upvote 0
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


 
Upvote 0
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
 
Upvote 0
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)

Code:
' 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
Code:
Sub ShowUF()
    Userform1.Show
End Sub
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top