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.
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.
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).
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.
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 |
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.