VBA Remove Duplicates in ComboBox

Home/VBA/VBA Remove Duplicates in ComboBox

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

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


VBA code to Remove Duplicates in ComboBox

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

VBA Remove Duplicates in ComboBox: Procedure

Following is the VBA Syntax and sample VBA macro command to delete duplicate in ComboBox1 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 ComboBox1.

VBA Remove Duplicates in ComboBox: Examples

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

Sub sbRemove_Duplicates_From_ComboBox()
Dim iCntr As Long
Dim recCountBefore As Long
Dim lRow As Long
Sheets("Test").ComboBox1.List = Array(1, 2, 3, 4, 5, 3, 2, 6, 7, 5, 3, 3)
recCountBefore = Sheet7.ComboBox1.ListCount
Set tmpSht = ThisWorkbook.Worksheets.Add
For iCntr = 0 To recCountBefore - 1
tmpSht.Cells(iCntr + 1, 1) = Sheet7.ComboBox1.List(iCntr)
Next
tmpSht.Columns(1).RemoveDuplicates Columns:=Array(1)
lRow = tmpSht.Range("A60000").End(xlUp).Row
Sheets("Test").ComboBox1.Clear
For iCntr = 1 To lRow
Sheets("Test").ComboBox1.AddItem tmpSht.Cells(iCntr, 1)
Next
Application.DisplayAlerts = False
tmpSht.Delete
Application.DisplayAlerts = True
MsgBox "Actual Items in the ComboBox: " & recCountBefore & vbCr _
& "Unique Items in the ComboBox: " & lRow & vbCr _
& "Duplicate Items Found in ComboBox: " & recCountBefore - lRow, vbInformation
End Sub

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

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

  • 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 combobox (rename as ComboBox1), 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 comboBox and then removing the duplicate items and finally you will find the 7 unique items in the comboBox. For better understanding, you can create two combo boxes and populate the unique items into combobox2. No you can clearly see the difference, all items in the combobox1 and unique items in combobox2.

Explained VBA Code to Delete Duplicates from an array

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

Sub sbRemove_Duplicates_From_ComboBox()
‘Declaring variables
‘iCntr to loop through the comboBox items.
Dim iCntr As Long
‘Variable for capturing the number of items in the ComboBox1 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 combobox.
Sheets(“Test”).ComboBox1.List = Array(1, 2, 3, 4, 5, 3, 2, 6, 7, 5, 3, 3)

‘Getting the number of items beore removing the items from comboBox.
recCountBefore = Sheet7.ComboBox1.ListCount

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

‘Getting the values from combobox to temporary worksheet.
For iCntr = 0 To recCountBefore – 1
tmpSht.Cells(iCntr + 1, 1) = Sheet7.ComboBox1.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 comboBox and reloading the unique items from temporary sheet to combobox.
Sheets(“Test”).ComboBox1.Clear
For iCntr = 1 To lRow
Sheets(“Test”).ComboBox1.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 ComboBox: ” & recCountBefore & vbCr _
& “Unique Items in the ComboBox: ” & lRow & vbCr _
& “Duplicate Items Found in ComboBox: ” & recCountBefore – lRow, vbInformation

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

LIMITED TIME OFFER
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. ragu April 15, 2017 at 2:11 AM - Reply

    Excellant piece of work service, thank u so much

Leave A Comment


Related pages


macro delete rows based on cell valuedelete blank rows excel 2010nested vlookupsvba write to text filewrite macro in excelvba workbooksinsert check box excelstring to int vbacost analysis template excelcountif examplevlookup formula in excel 2007 with exampleunhide column a excel 2007ms excel tutorials with examplesexcel 3d surface chartms excel formulas list with exampleshow to write excel macrouse of vlookup and hlookup in excelxml generator from excelwhat is ddl and dml in sql serversql interview question answer pdfvba color indexvba formulasactivate developer tab in excelvba selectshortcut key to change sheet in exceldcl in dbmsexcel select worksheetundo macro excelconditional formatting vlookuphow to learn vba in excelvba function syntaxexcel programming tutorialdelete blank rows vbaexcel function hyperlinkinserting pivot table in excel 2010dml dclexcel vba wait functionhow to unprotect an excel fileexcel formula hlookup exampleexcel macro locationpassword vba excelhow to learn macros in excelexcel how to delete duplicatesautosave in excel 2007sql sascell shading in excelpassword protect spreadsheetfaq testing interview questions answershow to access vba in excel 2007vba refresh pivot tablebasic sql interview questions and answers for freshersvba case functionexcel vba select multiple rowsmerge multiple cells in excelexcel vba offsethow to deduplicate in excelexcel vba messageboxexcel vba range &how to hide unhide rows in excelvba find duplicatesunprotectscreenupdatingconsolidate spreadsheetshow to copy data validation to other cellshow to unhide all columnsvba message box yes noremove excel duplicatesunhide a row in excel 2010ms excel row functionhow do i create a pivot table in excel 2007datevalue functionexample of dmlexcel vba on cell changeexcel vba on cell changehow to add developer tab to ribbon in word 2010how to record macro in excel 2007project gantt chart template excelautofit excel 2010dashboards for excel