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.