Help me speed up my macro as it writes to a worksheet

[000Steve000]

I currently have a large amount of data (10000 rows x 10 columns) of string data held in an array during the execution of a macro. I would like to write this to an empty sheet. I am currently using a for and next loop like:

For 1 = 1 to 10000
   worksheets(1).cells(i,1) = Myarray(i,1)
   worksheets(1).cells(i,2) = Myarray(i,2)
   'etc up to 10
next i

The macro takes 56 seconds on my computer and this bit of it is the main culprit. Does anyone have any ideas how I can speed up the process?

 

[Molby]

Set 'Application.ScreenUpdating = False' then back to true after the macro has finished pasting.

 

[000Steve000]

Thanks for that - good tip. In this case however, worksheets(1) is not the active sheet so the screen is not updating as it runs. I tried it just in case but the overall time was the same.

 

[Molby]

In that case I point you to the below thread, Geoff has some pointers on pasting arrays. thread707-895282

 

[SkipVought]

Hi,

faq707-4105 How Can I Make My Code Run Faster?

Skip,
[sub]
[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive! [/sub]

 

[Ravalia]

Might be a little late, but I've only just joined this forum:

How about the following:

worksheets(1).cells(1,1).resize(10000,10).value2 = myArray

 

[taupirho]

Try using the cipboard and paste buffer, should go like s**t off a shovel. Be interested in your revised timings when implemented. Here some sample code:-

Dim mydata As DataObject 
dim db_block as String 
Set mydata = New DataObject 


For i = 1 to 10000
   db_block = Myarray(i,1) & vbtab & myarray(i,2)
   'etc up to 10
   db_block = db_block & vbcrlf
next i

mydata.SetText Mid(data_block, 1) 
mydata.PutInClipboard 
worksheets(1).Cells(1, 1).Select 
Activesheet.paste

 

[byundt]

I agree with Ravalia that by far the fastest way of transferring the data between worksheet and VBA is to write it all at once using a variant array. The array must be two-dimensional with 1 base, and it must be a variant!

Dim Myarray(1 to 10000,1 to 10) As Variant
'Your code
Worksheets(1).Range("A1:J10000").Value= Myarray

Note that earlier versions of Excel had limits on the size of arrays that may be transferred back to Excel. Even after dealing with this complication, array transfer is still enormously faster than looping through the cells.