Efficiently Moving Data From An Array To A Worksheet

[mavest]

I am passing alot of data to a VBA routine as a delimited string, then I parse it into an array, and finally enter the data into a worksheet.

The string may parse into an array of 1000 to 2000 rows and 100 columns.

I realize that I could move the data from the array into the worksheet through a couple of for loops such as:

for row = 1 to 2000
for col = 1 to 100
worksheets("Sheet1").range("A1:A1").cells(row,col).value = BIGdataArray(row,col)
next col
next row

But I was wondering if there is a quicker more efficient way of moving the data from an array to the worksheet.

 

[PHV]

A starting point:
Range(Cells(1, 1), Cells(2000, 100)) = BIGdataArray

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mavest]

As you suggested the following syntax worked!

worksheets("Sheet1").range(Cells(1,1), Cells(2000,100)) = BIGdataArray

Very nice.

Off hand, do you know if this syntax just represents an internal loop, i.e. my FOR loops condensed into a single statement. Or is the syntax more process time efficient such as changing the value of a pointer to point to a different range in memory and not actually moving the data. Just wondering what type of time improvements I will see.

Final question: What if BIGdataArray was not an array, but an XML formated string, or some other delimited sting? (Background: the data will come to my VBA routine as a string and I had planned to use either SPLIT() or an XML parser to load the data into BIGdataArray().) Any quick way to read that into a range object?

Thanks for your help!


-Mike