Populating a combobox from a table - How much is too much?

[brucemc777]

I do not know if i have reasonable reason to be concerned about this, but i am pretty certain you folks would know-

I have a combobox which i wish to populate with three non-contiguous columns from a table. The table, at present, has about 550 records (rows) and 36 fields (columns). Of course the data i want reside in columns 1,17 and 36...

In similar instances i:

1. Copy the columns to a nice empty area of a worksheet next to one another
2. Use combobox.list = range.currentregion.value to populate the box
3. Erase the region

I do so because i am afraid that using the entire table and just giving widths to the columns i want will have detrimental effects on performance by overtaxing memory and/or any other resources by loading the entire table, though i notice that my present method takes a hit in terms of time to process the copy/pastes, even when i do so on a remote location of the same worksheet (as opposed to another worksheet, presuming this has any bearing whatsoever). As an added note, the table can potentially grow to an unknown number of records.

I believe (and i am embarrassed to say i have not run a time trial) that running loops and reading the data into an array would be more time consuming.

Is there a "rule of thumb" that would guide me as to when to use my method rather than just using the entire dang table as my list source, or am i worried about something that isn't a concern (due to reasons that i certainly don't understand as of yet!)? Just as, if not more, important, is there a way to do this that perhaps i have not considered?

Thank-You Very Much, as ALWAYS!

[Norie]

You wouldn't need to run a loop to load the table into an array, that could be done in one go.

If you only wanted to display certain columns from the table in the listbox you could load a second array from the first but since you wouldn't be reading from a worksheet that would be pretty fast.

Please Login or Register  to view this content.

[bakerman2]

Maybe this might help.

Please Login or Register  to view this content.

[brucemc777]

Thank-You!

In that i have learned over time and prior outstanding assistance to trust the advice of Norie and bakerman2 inherently, i infer that loading an entire table of this size as a combobox listsource is not a good idea, and that i really need to consider revising my other instances of copying over columns and then using the copied columns as my listsource, then deleting them, over to the array methods as shown above.

Very much appreciated! I am looking forward to experimenting with both methods.

Best Regards,
-Bruce(mc777)

[bakerman2]

Thanks for feedback and kind words.

As always you're welcome and thanks for rep+.