write a collection to a range

A collection cannot be written directly to a work sheet.

This code will take the elements of a collection and write them to a sheet. (Usually a collection holds objects, not values.)

Please Login or Register  to view this content.

I'm curious about your collection. Frequently, collections hold objects, not variables, so writing them to a sheet involves a property of the items in the collection, not the item itself.

Also, there is no such thing as a 2D collection. All collections are one dimensional.

Originally Posted by mikerickson

A collection cannot be written directly to a work sheet.

This code will take the elements of a collection and write them to a sheet. (Usually a collection holds objects, not values.)

Please Login or Register  to view this content.

I'm curious about your collection. Frequently, collections hold objects, not variables, so writing them to a sheet involves a property of the items in the collection, not the item itself.

Also, there is no such thing as a 2D collection. All collections are one dimensional.

Thanks Mike! I use the collection to get unique values for a combo box items. Good to know there is only one dimension in collections. I can only deal with one column

However I always have to redim preserve and then transpose the array to get the values, even for 1 column:

Please Login or Register  to view this content.

Thanks Mike! I use the collection to get unique values for a combo box items.

Perhaps you'd be interested in these codes. Neither uses a collection.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Originally Posted by mikerickson

Perhaps you'd be interested in these codes. Neither uses a collection.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Thanks for the codes. Brilliant! I am still going to use the collections this time though. Try to force myself to learn new ways of doing things.