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
Another
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
ActiveSheet.Calculate
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.
