Optimising VBA for Excel 2003: Avoid EntireRow.Insert

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.