Sorting and removing duplicates in a Combobox

[Andelousiano]

Hi,
I've tried searching online for a solution and haven't found anything that doesn't give me all sorts of errors, so I'll ask.

I have a userform which has a bunch of comboboxes. Several of these I want to populate from a column in the worksheet, and have them listed in alphabetical order and without duplicates and blanks.

So far I've been able to populate the combobox without blanks, but I'm having trouble removing duplicates and sorting.

Here's the code I'm using.

Please Login or Register  to view this content.

What can I add to this so that the duplicates are removed and the list is sorted?

[mehmetcik]

Your Code so far will work but it will be will very slow.

If you have 200 000 rows of data then VBA will have to interface with Excel 200 000 times to read the data in each cell.

You can achieve the same result without using a loop to read your cells into VBA.

Please Login or Register  to view this content.

[kev_]

You can do all that work with arrays - but because it's all done in memory it's not so easy to see what the VBA is doing if something is not doing what you want.

I prefer to create a new sheet to hold all the lists, getting VBA to remove empty cells and duplicates before sorting and naming them.

Do you require the lists to be dynamic?
- will the data in various columns change over time
(so that the alphabeticized, de-duped, de-blanked list needs a regular refreshing?)

[mike7952]

Try this

Please Login or Register  to view this content.

Missed the sorting part. But this should do

Please Login or Register  to view this content.

[Andelousiano]

Thank you Mike!! The second part worked flawlessly.

[Andelousiano]

One more question. If I wanted to do the same thing for several other comboboxes on the same form (linked to different columns of course), how would I go about that. I tried just copying and pasting that code within the same sub and changing the column letters and combobox name, but it gives me errors.

[Andelousiano]

Never mind. I figured out my mistake. The cell values needed to be text.