Array speed
 

I use the following code to write the data from a (8615 * 24) array to
worksheet
It is very fast before.
But I don't know why it is slow now.
Today, I spend 2.5 hours for this.
I want to know why and how to speed up. Many thanks.

Dim i As Long, j As Byte
For i = LBound(RMaster) To UBound(RMaster)
For j = 0 To 23
Cells(i + 2, j + 1 = RMaster(i, j)
Next
Next

Array speed
 

I put the code below in a new workbook and it took under 10 seconds. Try
doing hte same. I think it is the workbook and not the macro. You may have
a lot of data or fromating in the workbook which would slow down the macro.
But it also may be your PC. I would try putting the workbook on another PC
to see what happens.

Sometimes deleting rows that are not used in teh worksheet may help. If you
worksheet goes to column Z then highlight all the columns to the right of you
data and delete the columns not used. Repeat for the rows. Try for all
worksheets.


Sub test()

Dim RMaster(8615, 24)
Dim i As Long, j As Byte
For i = LBound(RMaster) To UBound(RMaster)
For j = 0 To 23
Cells(i + 2, j + 1) = RMaster(i, j)
Next
Next


End Sub


"leungkong" wrote:

I use the following code to write the data from a (8615 * 24) array to
worksheet
It is very fast before.
But I don't know why it is slow now.
Today, I spend 2.5 hours for this.
I want to know why and how to speed up. Many thanks.

Dim i As Long, j As Byte
For i = LBound(RMaster) To UBound(RMaster)
For j = 0 To 23
Cells(i + 2, j + 1 = RMaster(i, j)
Next
Next

Array speed
 

I want to know why and how to speed up.
Dim i As Long, j As Byte


Hi. Just another approach...

Sub Demo()
Dim RMaster(8615, 24)
Dim d
'// Load RMaster w/ data...etc

d = Dimensions(RMaster)

[B1].Resize(d(0), d(1)) = RMaster
End Sub


Private Function Dimensions(m) As Variant
Dimensions = Array( _
UBound(m, 1) - LBound(m, 1) + 1, _
UBound(m, 2) - LBound(m, 2) + 1)
End Function


Dim i As Long, j As Byte

I believe the consensus here in the newsgroup is that "Byte" is not
often used for a looping type. There just doesn't seem to be any speed
improvement.

Note that the number of columns is 25 (ie 0 to 24)
Perhaps you could use something like this:

Dim RMaster(1 To 8615, 1 To 24)

= = = = = = =
HTH :)
Dana DeLouis



leungkong wrote:
I use the following code to write the data from a (8615 * 24) array to
worksheet
It is very fast before.
But I don't know why it is slow now.
Today, I spend 2.5 hours for this.
I want to know why and how to speed up. Many thanks.

Dim i As Long, j As Byte
For i = LBound(RMaster) To UBound(RMaster)
For j = 0 To 23
Cells(i + 2, j + 1 = RMaster(i, j)
Next
Next