populating and sorting values in a dynamic array and returning for a combobox

Hello,

I have 2 columns of data, say 100 names in col 1 and an activity state (0, 1) in col 2.

What I want to do is pick up any record name that is active (1), insert into an array (that can change size as names are added), alpha-sort that array, and then make those values in the array available as the dropdown inside of a combobox.

Normally, I'd approach this without using an array and use a temporary sheet to move over the active names, use the excel alpha sort, then pick up the resultant cells into the combobox. This works, but is inelegant and it's time for me to learn how to use arrays properly. Any help would be most appreciated.

My typical procedure:

Please Login or Register  to view this content.

I can't help but think that using arrays would be a lot cleaner. Especially when the lists grow into 10s of thousands of rows and the lag time in looping thru & moving records starts being measured in minutes...

Hi Ouka,

Your code looks pretty good - maybe:

Please Login or Register  to view this content.

Interesting. There's some syntax in there that I haven't used before. Ima gonna pull it apart and see what it does, thanks!

I've been poking at arrays (never used them before, hard for me to trust that all is as it should be when I can't see them =p) and came up with the following:

Please Login or Register  to view this content.

I sort of understand what is going on in here; it was a lot of trial and error to get this snippet to work. Any help tightening it up would be appreciated. i also can't figure out why the top value in the combobox dropdown is a blank.

Hi Ouka,

you don't need all that f.r.

Please Login or Register  to view this content.