Problems in copying a large array (200000x24) to excel 2007

[muditmittal1]

Hi All,

I am facing problems in facing a very huge array (200000x24) fro VBA to excel. Here is what I am doing

Reading a larger dataset from excel to vba using range object
processing the dataset in VBA
trying to write it back to excel

While writing back to excel I am directly writing the array to a range. I have also tried breaking the dataset into arrays of 10x24 and writing them to excel. In whatever combination I try, I am not able to write beyong 11858 rows (surprisingly) and that to on different computers with different processors with different cache memory and different virtual memory

I have tried out numerous solutions (like passing array by reference or by value, breaking up the array into chunks, etc) on internet and this forum but achieved no success. Writing cell by cell is too slow and will not work for me.

This is my first post so please ignore any inconsistencies with the posting rules.

Please someone help me

very basic code without breaking into chunks or anything is as follows

Sub paste_data(ByRef collated_array)
Dim pasterange As Range
Set pasterange = Worksheets("output").Range(Cells(1, 1), Cells(UBound(collated_array), 24))
pasterange.Value = collated_array
End Sub

Regards

 

[shg]

What's in the array?

This line of code will fail if output is not the active sheet, because Cells refers to the active sheet:

Set pasterange = Worksheets("output").Range(Cells(1, 1), Cells(UBound(collated_array), 24))

It could be rewritten as

Sub paste_data(ByRef myArray)
    Worksheets("output").Range("A1").Resize(UBound(myArray, 1), UBound(myArray, 2)).Value = myArray
End Sub

... assuming the array lower bounds are both 1.

 

[muditmittal1]

the array is an array of strings and might contain a lot of data sometimes. I am only pasting to the activeworkbook so that is not an issue.