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
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:
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!
