Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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.

Populate combo box with unique values only by using VBA code


arrow blue right bubble Populate combo box with unique values only by using VBA code

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:

doc combobox unique values 1

2. Then right click the combo box, and choose View Code from the context menu, see screenshot:

doc combobox unique values 2

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:

doc combobox unique values 3

5. And then click OK button, only the unique values are displayed in the combo box, see screenshot:

doc combobox unique values 4

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.


Recommended Productivity Tools for Excel

kte tab 201905

Kutools for Excel Helps You Always Finish Work Ahead of Time, and Stand Out From Crowd

  • More than 300 powerful advanced features, designed for 1500 work scenarios, increasing productivity by 70%, give you more time to take care of family and enjoy life.
  • No longer need memorizing formulas and VBA codes, give your brain a rest from now on.
  • Become an Excel expert in 3 minutes, Complicated and repeated operations can be done in seconds, 
  • Reduce thousands of keyboard & mouse operations every day, say goodbye to occupational diseases now.
  • 110,000 highly effective people and 300+ world-renowned companies' choice.
  • 60-day full features free trial. 60-day money back guarantees. 2 years of free upgrade and support.

Brings Tabbed Browsing and Editing to Microsoft Office, Far More Powerful Than The Browser's Tabs

  • Office Tab is designed for Word, Excel, PowerPoint and Other Office Applications: 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!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.