VBA to Remove Duplicates in ListBox Excel

Home/VBA/VBA to Remove Duplicates in ListBox Excel

VBA to Remove Duplicates in ListBox Excel Macros Examples Codes: to delete duplicate records from a List Box in MS Excel 2003, 2007, 2010, 2013. We will also see the practical example for deleting the duplicates from a List Box.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

BROWSE ALL TEMPLATES

50+ Project Management Templates Pack
Excel PowerPoint Word

VIEW DETAILS

Advanced Project Plan & Portfolio Template
Excel Template

VIEW DETAILS

Ultimate Project Management Template
Excel Template

VIEW DETAILS

20+ Excel Project Management Pack
Excel Templates

VIEW DETAILS

20+ PowerPoint Project Management Pack
PowerPoint Templates

VIEW DETAILS

10+ MS Word Project Management Pack
Word Templates

VIEW DETAILS


VBA remove duplicates in listBox

VBA code to Remove Duplicates in ListBox

Here is the Example VBA syntax and Example VBA Macro code to Remove Duplicates in a List Box in Excel. This will help you to know how to delete duplicate records from a ListBox using VBA.

VBA Remove Duplicates in ListBox: Procedure

Following is the VBA Syntax and sample VBA macro command to delete duplicate in ListBox1 using VBA. In this method we will first get the data into Excel worksheet and remove the duplicate using RemoveDuplicates method and then reload into the ListBox1.

VBA Remove Duplicates in ListBox: Examples

The following Excel VBA macro code is to delete duplicate in a List Box. This VBA macro will add a temporary worksheet. And enter the items from ListBox to temporary worksheet. Then remove the duplicate records in the temporary worksheet. And then reload the unique items into ListBox. Finally it deletes the temporary worksheet.

Sub sbRemove_Duplicates_From_ListBox()

Dim iCntr As Long
Dim recCountBefore As Long
Dim lRow As Long

Sheets("Test").ListBox1.List = Array(1, 2, 3, 4, 5, 3, 2, 6, 7, 5, 3, 3)
recCountBefore = Sheet7.ListBox1.ListCount
Set tmpSht = ThisWorkbook.Worksheets.Add

For iCntr = 0 To recCountBefore - 1
    tmpSht.Cells(iCntr + 1, 1) = Sheet7.ListBox1.List(iCntr)
Next

tmpSht.Columns(1).RemoveDuplicates Columns:=Array(1)
lRow = tmpSht.Range("A60000").End(xlUp).Row

Sheets("Test").ListBox1.Clear
For iCntr = 1 To lRow
    Sheets("Test").ListBox1.AddItem tmpSht.Cells(iCntr, 1)
Next

Application.DisplayAlerts = False
    tmpSht.Delete
Application.DisplayAlerts = True

MsgBox "Actual Items in the ListBox: " & recCountBefore & vbCr _
& "Unique Items in the ListBox: " & lRow & vbCr _
& "Duplicate Items Found in ListBox: " & recCountBefore - lRow, vbInformation

End Sub

Instructions to run the VBA Macro code to delete duplicates in ListBox

Please follow the below steps to execute the VBA code to delete duplicates in ListBox.

  • Step 1: Open any Excel workbook
  • Step 2: Press Alt+F11 – This will open the VBA Editor
  • Step 3: Insert a code module from then insert menu
  • Step 4: Copy the above code and paste in the code module which have inserted in the above step
  • Step 5: Inset a new worksheet and place ListBox (rename as ListBox1), rename the sheet as “test”
  • Now press F8 to debug and test the code

Now you can observe the code initially adding 12 items in the ListBox and then removing the duplicate items and finally you will find the 7 unique items in the ListBox. For better understanding, you can create two list boxes and populate the unique items into listbox2. No you can clearly see the difference, all items in the ListBox1 and unique items in ListBox2.

Explained VBA Code to Delete Duplicates from an array

Starting Macro program and sub procedure to write VBA code to delete duplicate records in ListBox.

Sub sbRemove_Duplicates_From_ListBox()
‘Declaring variables
‘iCntr to loop through the ListBox items.
Dim iCntr As Long
‘Variable for capturing the number of items in the ListBox1 initially.
Dim recCountBefore As Long

‘lRow to store the last Row in the worksheet after removing the duplicates.
Dim lRow As Long

‘adding some items in the ListBox.
Sheets(“Test”).ListBox1.List = Array(1, 2, 3, 4, 5, 3, 2, 6, 7, 5, 3, 3)

‘Getting the number of items beore removing the items from ListBox.
recCountBefore = Sheet7.ListBox1.ListCount

‘Adding a temporary worksheet.
Set tmpSht = ThisWorkbook.Worksheets.Add

‘Getting the values from ListBox to temporary worksheet.
For iCntr = 0 To recCountBefore – 1
tmpSht.Cells(iCntr + 1, 1) = Sheet7.ListBox1.List(iCntr)
Next

‘Removing the duplicates in the worksheets.
tmpSht.Columns(1).RemoveDuplicates Columns:=Array(1)

‘finding last row after deleting the duplicates.
lRow = tmpSht.Range(“A60000”).End(xlUp).Row

‘Clearing the ListBox and reloading the unique items from temporary sheet to ListBox.
Sheets(“Test”).ListBox1.Clear
For iCntr = 1 To lRow
Sheets(“Test”).ListBox1.AddItem tmpSht.Cells(iCntr, 1)
Next

‘Stopping alerts and delting worksheet
Application.DisplayAlerts = False
tmpSht.Delete
Application.DisplayAlerts = True

‘displaying the message.
MsgBox “Actual Items in the ListBox: ” & recCountBefore & vbCr _
& “Unique Items in the ListBox: ” & lRow & vbCr _
& “Duplicate Items Found in ListBox: ” & recCountBefore – lRow, vbInformation

End Sub
End statement to end the VBA code to delete duplicate records in ListBox.

LIMITED TIME OFFER - Get it Now!
Advanced Project Plan Excel Template

 
Related Resource External VBA Reference
By |January 19th, 2015|VBA|1 Comment

About the Author:

PNRao is a passionate business analyst and having close to 10 years of experience in Data Mining, Data Analysis and Application Development. This blog is his passion to learn new skills and share his knowledge to make you expertise in Data Analysis (Excel, VBA, SQL, SAS, Statistical Methods, Market Research Methodologies and Data Analysis Techniques).

One Comment

  1. kapil August 20, 2016 at 3:47 PM - Reply

    I have tried the code -remove duplicates but it seems to have problem while clearing the listbox1. don’t know why? please help.
    here’s the code below.

    Sub remove_duplicates()
    Dim lstrow As Long
    Worksheets.Add
    ActiveSheet.Name = “temp”
    For i = 0 To ListBox1.ListCount – 1

    Sheets(“temp”).Cells(i + 1, 1) = ListBox1.List(i)
    Next i
    MsgBox ActiveSheet.Name
    ThisWorkbook.Worksheets(“temp”).Columns(1).RemoveDuplicates Columns:=1
    lstrow = ThisWorkbook.Worksheets(“temp”).Range(“A60000”).End(xlUp).row
    Sheets(“userform-mak”).Shapes(“listbox1”).clear
    ‘ActiveSheet.Shapes(“TextBox1”).Delete

    For i = 1 To lstrow
    Sheets(“userform-mak”).ListBox1.Add ThisWorkbook.Worksheets(“temp”).Cells(i, 1)

    Next i
    ThisWorkbook.Worksheets(“temp”).Delete

    End Sub

Leave A Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.