DataWright Information Services

Consulting and Resources for Excel and Access

Some operations run faster without loops

VBA, like all programming languages, has a number of looping
structures that let you work your way through a group of items
(cells, columns, worksheets etc) and do things to each member of
that group. In many cases a loop is as good as any other method but
sometimes, you can process all items in one step. Inserting or
deleting rows and columns would be a typical example.

This article shows several techniques for turning loops into
one-step operations. The result is almost always faster code. It
will most likely be updated with new examples over time.

Example 1: Inserting or deleting multiple columns

article on this site
shows how to insert and delete multiple
columns as a way to drill up and down in an Excel report. This
example is designed to show the difference that you can make by
avoiding unnecessary calculation steps, and by doing insertions and
deletions in one step.

The test workbook was large, computationally intensive, and
needed calculations to be set to Manual in order to be usable.
Because of those factors the difference between single step and
looping is exaggerated. Three versions of the code were tested,
using the VBA Timer function to record the duration of each run. The
results are the average of four runs, repeated with different
numbers of columns. The code is shown for an insertion of 6 columns.

Starting point — looping code, calculating after each run
through the loop

StartTime = Timer
For i = 1 to 6   
    Cells(1, CurCol).EntireColumn.Copy
    Cells(1, CurCol).Insert shift:=xlToRight
Next i
EndTime = Timer
Debug.Print "Insert Loop + Calc: " & (EndTime-StartTime)

This ran very slowly when multiple columns were inserted. The
first optimisation was to remove the calculation step from the loop
(there was a final calculation, not shown in the code, so the one in
the loop was not required).

First optimisation — remove the calculation step

StartTime = Timer
For i = 1 to 6   
    Cells(1, CurCol).EntireColumn.Copy
    Cells(1, CurCol).Insert shift:=xlToRight
Next i
EndTime = Timer
Debug.Print "Insert Loop: " & (EndTime-StartTime)

You can see the effect of this step in the following table. Times
are in seconds.

Method Insert 5 columns Insert 17 columns
Loop with calculation 6.4725 21.185
Loop without calculation 1.3375 4.7225

Just removing the calculation made a big difference but inserting
large numbers of columns still took too long. The second
optimisation replaced the loop with a single step that used the
Resize method to insert the required number of columns.

Second optimisation — remove the loop

StartTime = Timer
    Cells(1, CurCol).EntireColumn.Copy
    Cells(1, CurCol).Resize(1, 6).Insert shift:=xlToRight
EndTime = Timer
Debug.Print "Insert Resize: " & (EndTime-StartTime)
Method Insert 2 columns Insert 16 columns
Single step 0.2275 0.2275

Take-home message

By comparing the times taken for these three approaches, there
are a couple of points that can help you to speed up your VBA code.

  • First, leave out calculation steps in loops unless you
    really have to calculate a column before proceeding. If that is
    the case, consider calculating just the selection instead of the
    entire sheet.
  • Second, when you use a loop to insert or delete cells, the
    time taken scales with the number of columns. If you do it in
    one step, the time taken is independent of the number of columns
    inserted or deleted.