Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to 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.
156 Views
