Populating a ComboBox with unique values in Excel can be very useful when it comes to database building, or where a ComboBox has multiple values and it would be easier to populate the values from a range of cells that could be dynamic.
This can be done in a number of ways, using advanced filter, copying a range, removing duplicates and putting the new list in a particular range that the ComboBox is instructed to read from, but bt far the simplest solution is to use the “scripting.dictionary” functions and compare/count text values as they are analysed, preventing any that already exist in the scripting.dictionary to be ignored for the purpose of populating the list.
Datanology use this code on the majority, if not all their Excel database products because it is so versatile, straightforward and is a relatively short piece of code to execute at any point of a codes process. The ComboBox we refer to is named ComboBox1 and the range is on the Data tab from A2 to A100. This can be adapted in the code to whatever you need it to be, and you can even integrate the lastrow VBA that we deal with in other parts of this blog. It can be a powerful process to add to your userforms and database controls.
If you have any questions, feel free to ask us or leave a comment below for us to get back to you.
Public Sub Populate_combobox_with_Unique_values()
Dim v, e
v = .Value
.comparemode = 1
For Each e In v
If Not .exists(e) Then .Add e, Nothing
If .Count Then Me.ComboBox1.List = Application.Transpose(.keys)