Writing huge amount of data into Excel spreadsheet can be time consuming task even on high end computers. Very easy trick to speed it up is to store values into array and write them in blocks. Performance gain can be really substantial.
Example macro code below stores 1 300 000 values. It takes on tested computer 58 seconds to complete by writing single values and 5 seconds (!) by writing each 20 values as whole row.
Sub WriteVals()
Dim x, y
Dim ar(19)
' write single values
Cells(1, 1) = Now()
For y = 1 To 65000
For x = 1 To 20
Cells(y + 1, x) = x + y
Next
Next
Cells(1, 2) = Now()
Cells(1, 3).Formula = "=(B1-A1)*3600*24"
' write 20 values in row
Cells(1, 4) = Now()
For y = 1 To 65000
For x = 1 To 20
ar(x - 1) = x + y
Next
Range("A" & y + 1 & ":T" & y + 1).Value = ar
Next
Cells(1, 5) = Now()
Cells(1, 6).Formula = "=(E1-D1)*3600*24"
End Sub
Further optimization can be made, for example using 2D arrays.
Enjoy and speed up!
