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

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

> Dim RMaster(8615, 24)
> Dim i As Long, j As Byte
> For i = LBound(RMaster) To UBound(RMaster)
> For j = 0 To 23

As a side point for the OP... you didn't have to hard code the limits for the second For statement... you could use LBound and UBound for them to, just use the optional second argument to get to the second dimension's bounds.

Dim RMaster(8615, 24)
.......
For i = LBound(RMaster) To UBound(RMaster)
For j = LBound(RMaster, 2) To UBound(RMaster, 2)
......

--
Rick (MVP - Excel)


"Joel" <(E-Mail Removed)> wrote in message news:6A5A3B49-A4C5-4E8E-9BA2-(E-Mail Removed)...
>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

This example runs in 0.3 seconds on my system: its much faster to transfer
the data in 1 block from the array than doing it cell-by-cell.

Sub test()

Dim RMaster(8615, 24)
Dim i As Long, j As Long

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

For i = LBound(RMaster) To UBound(RMaster)
For j = 0 To 23
RMaster(i, j) = i * j
'Cells(i + 2, j + 1) = RMaster(i, j)
Next
Next
Cells(2, 1).Resize(UBound(RMaster) + 1, UBound(RMaster, 2) + 1) =
RMaster

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True

End Sub

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"leungkong" <(E-Mail Removed)> wrote in message
news:7F0BBEB8-BECB-44CE-B842-(E-Mail Removed)...
>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
>

> 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