How to pass data from a range to Combobox without repeating it using object collection

A combo box is a very useful tool within Excel for creating forms, it is basically combining a text box with a drop-down list. If you really want to know how to pass information of a range to combo box without repeating duplicate values, keep reading this post.

How to pass data from a range to a combo box without repeating it using the object collection

It is time for you to know all the steps you need to take in order to pass the data of a range to the combo box without repeating it using the collection of objects:

1. The first thing to do is to open the spreadsheet with a series of records in one column, some of which are repeated multiple times.
2. Now you will select the "VBA" (Visual Basic Application) environment icon located in the upper left corner of the bar " Developer «.
3. In the next window, select » UserForm1 »In the left menu, your form will open and double click on the combo box.
4.  Subsequently, a window will open with programmable information that allows the combobox to automatically load the data. Here you will add the variable "Record as Interger" to the end of the second line and it should be like this "Dim Row As Interger, Final As Interger, Record as Interger".
5. Then, go to the end of line 9 and click on the «Enter» button and put «Register = WorksheetFuntion.CountIf (.Range (.Cells (1, 1) ,. Cells (Row, 1)) ,. Cells (Row, 1)) «and click» Enter «again.
6. Then, on the next line, open an If condition by writing the following code "If Record = 1 Then"
7. Next, you will go to the end of line 12, click on the "Enter" button and write down the "End If" command to end the If condition you had opened.
8. Finally, click on the green button " Play »Located in the top menu bar, so that the changes are saved.

At this point, you can go to create a form in Excel and when you open it, you will see how the data of a range is not repeated and only the unique values.

Advantages of passing data from a range to a combo box without repeating it

Generally, when you select a range of data, you will be able to find several repeating parameters within the same column that will be represented in the combo box or form. Sometimes, you will need unique values ​​to be recorded within the table where each of the data is accumulated (combobox).

By configuring combobox, so that it does not repeat the data in a column, you will only get original and unique data within the form. This is very useful when filtering data in a spreadsheet, where specific information is required.

Plus, you'll save valuable time, as you won't have to review the data for data to know if there are any duplications, as the tool will do it on its own. Also, you may be interested in knowing how to transpose or modify rows and columns using formulas in an Excel sheet.

Pass the data of an interval to combobox without repeating it Using the item collection is very quick and easy, thanks to the steps described above and it will only take you 2 minutes to do it. Don't forget to follow our blog where you'll get all kinds of office information you'll need to make your projects easier.