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.
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 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.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!