How to populate combo box with unique values only in Excel?
When you create a combo box with a list of data which include some duplicate values, the duplicate values will not be exclude automatically in the combo box. In this case, how could you display only unique items in your combo box? May be this tutorial can do you a favor.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
The following VBA code may help you to add the unique values when creating a combo box, please do as this:
1. Insert a combo box first, please click Developer > Insert > Combo Box (ActiveX Control), and then drag the mouse to draw a combo box, see screenshot:
2. Then right click the combo box, and choose View Code from the context menu, see screenshot:
3. And the Microsoft Visual Basic for Applications window is popped out, please copy and paste the following code to replace the original code.
VBA code: combo box with unique values:
Public Sub Populate_combobox_with_Unique_values() 'Updateby Extendoffice 20160913 Dim vStr, eStr Dim dObj As Object Dim xRg As Range On Error Resume Next Set dObj = CreateObject("Scripting.Dictionary") Set xRg = Application.InputBox("Range select:", "Kutools for Excel", _ ActiveWindow.RangeSelection.AddressLocal, , , , , 8) vStr = xRg.Value Application.ScreenUpdating = False With dObj .comparemode = 1 For Each eStr In vStr If Not .exists(eStr) And eStr <> "" Then .Add eStr, Nothing Next If .Count Then ActiveSheet.ComboBox1.List = WorksheetFunction.Transpose(.keys) End If End With Application.ScreenUpdating = True End Sub
Note: In the above code, the ComboBox1 is the name of your created combo box, you can change it as you need.
4. Then press F5 key to run this code, and in the prompt box, please select the data source list you want to add into the combo box, see screenshot:
5. And then click OK button, only the unique values are displayed in the combo box, see screenshot:
Tip: If there is no Developer tab in the ribbon, this article How to show/display developer tab in Excel 2007/2010/2013 Ribbon? may do a favor for you to display Developer tab.