While optimising an Excel 2003 VBA program to write thousands of rows of data, I noticed that Excel's
EntireRow.Insert consumed most of the program's processing time. To quantify the time, here's a simple test that just inserts rows into a workbook ...
Option Explicit Sub Insert10kRows() Dim wks As Worksheet Set wks = Sheets("Sheet1") Dim intI As Integer For intI = 1 To 10000 wks.Range("A1").EntireRow.Insert wks.Cells(1, 1).Value = "Test" Next intI End Sub Sub TestInsert() Dim douStart As Double Dim i As Integer For i = 1 To 6 douStart = Timer Insert10kRows Debug.Print i & "0000:" & Timer - douStart Next i End Sub
... and the results:
Rows Inserted Time 10000 7.703125 20000 11.125 30000 14.75 40000 18.9375 50000 23.40625 60000 28.140625
On the other hand, if you just write rows and advance a row counter, like this ...
Sub Write10kRows() Dim wks As Worksheet Set wks = Sheets("Sheet1") Dim intI As Integer For intI = 1 To 10000 wks.Cells(intI, 1).Value = "Test" Next intI End Sub Sub TestWrite() Dim douStart As Double Dim i As Integer For i = 1 To 6 douStart = Timer Write10kRows Debug.Print i & "0000:" & Timer - douStart Next i End Sub
... you get much better performance (low numbers are better).
Rows Written Time 10000 0.6875 20000 0.6875 30000 0.703125 40000 0.6875 50000 0.6875 60000 0.6875
Another observation is that the more rows exist in a worksheet, the longer Excel takes to insert rows (hence the nested loops in the sample code). I can imagine that Excel is spending a lot of time just shuttling data from one block of memory to another to make room for new rows.
For my program, the solution was to refactor the code to replace row insertion with a row counter and to write data to the appropriate row. It's a little more complicated but the program runs at least 10x faster.