|
FOR-TO_NEXT is *very* slow
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 ------------------------------- How can I speed up this Macro ? -- Med venlig hilsen Peter Hesselager-Olesen SBS-IT Aps |
FOR-TO_NEXT is *very* slow
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 "Peter Hesselager" wrote in message ... 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 ------------------------------- How can I speed up this Macro ? -- Med venlig hilsen Peter Hesselager-Olesen SBS-IT Aps |
FOR-TO_NEXT is *very* slow
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: 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" a écrit dans le message de groupe de discussion : ... 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 ------------------------------- How can I speed up this Macro ? -- Med venlig hilsen Peter Hesselager-Olesen SBS-IT Aps |
| All times are GMT +1. The time now is 07:20 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com