Populate Combo Box with Unique Values Only in Excel

In order to improve data administration and analysis, Excel is a strong application that provides a wide range of features. Combo boxes, usually referred to as drop-down lists, which let users choose alternatives from a predefined list, are one useful feature. Despite the fact that Excel comes with tools for creating combo boxes, there may be times when you wish to fill the combo box with particular values from a particular set of cells.

In this tutorial, we'll look at a step-by-step process for filling an Excel combo box with unique data. You may simplify your data entry process by following these guidelines, and you can make sure that consumers have a straightforward and error-free experience with combo boxes. So let's get started and discover how to fill an Excel combo box with distinctive values!

Populate Combo Box With Unique Values Only

Here we will first insert a combo box, then assign a macro to it to complete the task. So let us see a simple process to know how you can populate combo boxes with unique values only in Excel.

Step 1

Consider an Excel sheet where you have a list of items with duplicate values and also a combo box, similar to the below image.

First, right-click on the combo box and select View code to open the Application. Then copy the below code into the text box.

Right Click > View Code > Copy.

Code

Public Sub Populate_combobox_with_Unique_values()
   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:", "Populate Combo Box", _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

Step 3

Then click F5 to run the module. Then select the range of cells and click OK to complete the task.

F5 > Select Cells > Ok.

Then you can see that the combo box will be populated with the unique values. This is how you can populate a combo box with unique values only in Excel.

Conclusion

In this tutorial, we have used a simple example to demonstrate how you can populate a combo box with unique values only in Excel to highlight a particular set of data.