Write Collection as Range to Worksheet

[vthokienj]

How do you write the contents of a collection as a range instead of outputting cell by cell? I would like to write all 50 values with one command,
unlike below. Thanks.

    Dim mycol As Collection: Set mycol = New Collection
        
    ' fill the collection
    For x = 1 To 50
        mycol.Add (x)
    Next x

    ' output to sheet one at a time
    For x = 1 To 50
        Sheets("sheet1").Cells(x, 4) = mycol(x)
    Next x

 

[drsarao]

As far as I know, Collections.Items can be accessed sequentially ONLY and written to sheet. NOT enbloc. Better to use an array if you want to access sheet less often.

If you must use collection, then you can first write the Collection to an array in a loop and then write the array to sheet enbloc. Depending on the number of elements, this may still be faster. Though not as fast as using an array in the first place!

Incidentally, it is better to use Dictionary objects rather than Collections. Check this: http://www.cpearson.com/Excel/CollectionsAndDictionaries.htm This site also gives ways to translate between Collections/Dictionary/Arrays/Ranges.