FOR-TO_NEXT is *very* slow

[Peter Hesselager]

Hi
I have a Loop, that collects certain data-rows, and put them in an array.
This part works Ok and fast.

The next loop insert the data from the Array into another Sheet.
This is my problem.
In the old Excel 2003 it took maybe half a minute to write +600 rows
In Excell 2007 it takes +10 minutes.

Screenupdating is disabled.
There are several of this type Read and Write's, and the Macros now runs
well over half an hour, where it took 1-2 minutes before

This is how the "Insert" loop looks.
---------------------------
' Indsert lines in STRIP
Sheets("Strip").Select
Range("A1").Select
For Linje = 0 To NyLinjeMax
For Kolonne = 0 To 27
ActiveCell.Offset(Linje, Kolonne) = XAL_Data(Linje, Kolonne)
XAL_Data(Linje, Kolonne) = ""
Next
Next

 

[RB Smissaert]

You can write the array directly to a range, without a loop:

Sub test()

Dim r As Long
Dim c As Long
Dim arr(1 To 10, 1 To 5)

For r = 1 To 10
For c = 1 To 5
arr(r, c) = r & "@" & c
Next c
Next r

Cells.Clear

'to show that the range can be smaller than the array
Range(Cells(1), Cells(8, 4)) = arr

End Sub


RBS

 

[MichDenis]

Hi Peter,

Dim X As String
X = Application.Calculation
Application.Calculation = xlCalculationManual
With Sheets("Strip")
Application.EnableEvents = False
.Range("A1").Resize(UBound(XAL_Data, 1), UBound(XAL_Data, 2)) = XAL_Data
Application.EnableEvents = True
End With
Application.Calculation = X




"Peter Hesselager" <[email protected]> a écrit dans le message de
groupe de discussion : (e-mail address removed)...
Hi
I have a Loop, that collects certain data-rows, and put them in an array.
This part works Ok and fast.

The next loop insert the data from the Array into another Sheet.
This is my problem.
In the old Excel 2003 it took maybe half a minute to write +600 rows
In Excell 2007 it takes +10 minutes.

Screenupdating is disabled.
There are several of this type Read and Write's, and the Macros now runs
well over half an hour, where it took 1-2 minutes before

This is how the "Insert" loop looks.
---------------------------
' Indsert lines in STRIP
Sheets("Strip").Select
Range("A1").Select
For Linje = 0 To NyLinjeMax
For Kolonne = 0 To 27
ActiveCell.Offset(Linje, Kolonne) = XAL_Data(Linje, Kolonne)
XAL_Data(Linje, Kolonne) = ""
Next
Next

 

[Peter Hesselager]

The routine with xlCalculationManual does indeed the trick !
The macro now runs faster than it did in the old Excel 2003

In the meantime I found an old answer from Jim Thomlinsen (06-03-2007)
stating the same answer.

Is there an easy explanation as to why Automatic Calculation is so slow in
Excel 2007 compared to Excel 2003?


Thank's !!
--
Med venlig hilsen
Peter Hesselager-Olesen
SBS-IT Aps



"MichDenis" skrev: