Userform: Unique Values to ComboBox

Hi, dinosaur1993,

maybe like this:

Please Login or Register  to view this content.

Please alter the names of the sheet as well as the name of the combobox to suit.

Ciao,
Holger

Hi, dinosaur1993,

basicly your code is correct. I think you would move the code for setting the range into the With-Statement to have it work for Sheets(1) as well. And Rng is an element on itīs own, if you put it in like you did VBA doesnīt takre it as the range but as a variable:

Please Login or Register  to view this content.

Ciao,
Holger

Hi, dinosaur1993,

count the number of occurrances of one item in a given range - itīs the function you may use as a formula Countif in your excel sheet as well.

Ciao,
Holger

Hi, dinosaur1993,

regarding the list not covering only uniques: check the entries for leading and/or trailing sblanks.

About sorting: either wirte to a new workbook, sort there, read from there or use an array and sort that. Thirs option could be using a Dictionary or Collection.

Regarding nilemīs code: checking each cell value againts the string which was created and only add teh cell value if itīs not present in the string. InStr will retrun the position of the character of the forund item in the string, if 0 no match was found so add.

Ciao,
Holger

Hi, dinosaur1993,

the code doesn't really work right

You set the range starting at D5 but start the loop at row 1 - when altered that I get the result I would expect from it:

Please Login or Register  to view this content.

Ciao,
Holger

Hi, dinosaur1993,

the original solutions from both nilem and me make use of the UserForm_Initalize-event which is only triggered once: right at the point where the UserForm is created for use in the worksheet environment. You changed the code to an event which may be triggered multiple times and wonder why the code adds the items which is whatīs written in the code. So we better clear the contents before reading and adding again.

You should alter your procedure to read:

Please Login or Register  to view this content.

Ciao,
Holger

Hi, dinosaur1993,

if that requirement should be satisfied you may not compare the item to the range but only to the range from the start to the present row:

Please Login or Register  to view this content.

Ciao,
Holger

Hi, dinosaur1993,

just change the reference form Column D to Column C, and leave the rest alone (maybe alter the label caption as well).

Please Login or Register  to view this content.

Ciao,
Holger