Moving dictionary items to an 2D array that can be placed into worksheet quickly

Hi all,

From what I have learned at this site and books is that is you want to read from or write to a range of cells in excel quickly you have to use a "variant" array dimensioned something like this.

Dim example_array (1 to 400, 1 to 1)

One can assign the contents of this array to an appropriate range very very quickly.

Here is my question/problem:

I have a dictionary of items. The dictionary is created in the following manner....nothing fancy.

Please Login or Register  to view this content.

Now I need some way of getting the dictionary items out and into an array like the one I mentioned above that can be placed into a range quickly.


if I use this code

Please Login or Register  to view this content.

then junk_array become an array of the dictionary items but its not of the appropriate format and I can not put the contents of this array into a range of cells quicky.

So my question is how to get items out of a dictionary and into a variant array type that I can then place the contents into a range quickly.

Originally Posted by welchs101

if I use this code

Please Login or Register  to view this content.

then junk_array become an array of the dictionary items but its not of the appropriate format and I can not put the contents of this array into a range of cells quicky.

Why? It's just an array

sorry but I don't understand your question.

You specify that you cannot put the the array returned by the items property of the dictionary object directly into a range.

My question is simply why can't you? Have you tried it?

I would guess that in our example that the junk_array is simply an array of strings - there's no reason that you can't populate the worksheet directly with it

ok. can you provide the code that you would use to place junk_array into sheet1 starting in cell C1.

Please Login or Register  to view this content.

thanks.

just a note. Application.transpose wont work correctly if you have too many elements in the array. I forget what the number is .......I have run into it twice for large files.




So I have to transpose the array to paste it into range.........ok.

thanks.

I really appreciate your help.........

So I have to transpose the array to paste it into range

Yes, just like any array, the dictionary is a bit of a red herring since it isn't relevant

It's about 65k elements, it's fast to just create a 2d array and read the elements in before writing it to the sheet though if you think it may be an issue

Thanks.

I think this is what I have done in the past is create a 2D array and then take data "out" of dictionary and put into array like the following:

Please Login or Register  to view this content.