This is the second in a series of 7 blog posts explaining how to speed up data transformation in Excel VBA with a few simple techniques.
- Copy cells with VBA
- Add data to a table
- Open a file and transfer data to a target file
- Open a file and transfer data to a destination file
- Save data in a recordset
- Use a recordset to read data from multiple files without opening them
- Create a recordset based on data from 2 different worksheets
In this blog post, we’ll show you how much time you can save when adding a large number of cells to a table. If you use VBA code to add a large amount of data to a table, Excel will quickly get bogged down and its execution time will soar. This happens because Excel has to redefine the table structure, update formulas and edit the visual layout for each cell you add. The latter in particular isn’t Excel’s strong suit.
In this blog, we’ll start with a table with a limited number of rows and add a row. Next, we’ll set up a loop to repeat this action to see what happens to the execution time of the VBA code. After that, we’ll use an interesting trick to boost the execution speed.
The example above shows the table (range B4 to O9) and data in row 2. These are the data our loop will keep adding to the table.
In the previous blog post, we demonstrated that repeatedly copy-pasting data slows down the code’s execution time. That’s why in the first example, we’ll use a simple method to add the row to the table, outside the table. The example uses fixed values instead of variables.
Sub Macro4()
Range(Cells(10, 2), Cells(10, 15)) = Range(Cells(2, 2), Cells(2, 15)).Value
End Sub
Whenever you add data to a table, Excel needs to check the formatting of the row and the formulas it contains. The underlying address of the table also needs to be adjusted to the new area. This happens every time you add data to the table; in the next example, you’ll see what happens when we add the same row 10,000 times.
Sub Add10KRows_1()
Dim xRow As Long
Debug.Print Now
For xRow = 10 To 10000
Range(Cells(xRow, 2), Cells(xRow, 15)) = Range(Cells(2, 2), Cells(2, 15)).Value
Next xRow
Debug.Print Now
‘Code Execution 01:16
End Sub
This code’s execution time is 1:16 minutes.
Adding ‘ScreenUpdating’ (see the first blog post in this series) reduces this time to under one minute (0:57).
Sub Add10KRows_2()
Dim xRow As Long
Debug.Print Now
Application.ScreenUpdating = False
For xRow = 10 To 10000
Range(Cells(xRow, 2), Cells(xRow, 15)) = Range(Cells(2, 2), Cells(2, 15)).Value
Next xRow
Application.ScreenUpdating = True
Debug.Print Now
‘Code Execution 00:57
End Sub
Admittedly, the difference isn’t as spectacular as it was in the first blog post of this series about boosting the execution speed of VBA. This is because of the table itself.
Note: Don’t try to copy all cells to the table one by one, like we did in the previous blog post. You’ll ask the software to edit the table a whopping 140,000 times, which took an unholy amount of time on my laptop (10 to 15 minutes). After that, Excel will continue to struggle with even the simplest tasks; the only way to fix this is by restarting the application.
We’ll use another method to grab the same amount of cells (10,000 rows * 14 columns), paste them some distance below the table and then move everything to the table. This way, you’ll only add data to the table once instead of 10,000 or even 140,000 times.
This time, we’ll paste the data on row 15, which is 5 rows below the last row of the table. This means Excel won’t need to add anything to the table or change it. After that, we’ll move the cells one by one because this is the fastest way to work, as the first blog showed.
Sub Add10KRows_3 ()
Dim x As Long, y As Long
Debug.Print Now
Application.ScreenUpdating = False
For y = 15 To 10015 Step 1
For x = 2 To 15 Step 1
Cells(y, x) = Cells(2, x)
Next x
Next y
Application.ScreenUpdating = True
Debug.Print Now
‘Code Execution 00:07
End Sub
This only takes 7 seconds, which is considerably faster than the minute it took to perform the previous example. All we need to do now is complete the code by adding the new area to the table.
Sub CopyLoop()
Dim x As Long, y As Long
Debug.Print Now
Application.ScreenUpdating = False
For y = 15 To 10015 Step 1
For x = 2 To 15 Step 1
Cells(y, x) = Cells(2, x)
Next x
Next y
Range(“B15”).CurrentRegion.Cut Destination:=Range(“B10”)
Application.ScreenUpdating = True
Debug.Print Now
‘Code Execution 00:07
End Sub
The code takes about the same time to perform this task because the copy-paste action only needs to be executed once and Excel only needs to update the table once.
Note: If you execute actions involving a lot of data in VBA, it’s best to close Excel regularly while writing and testing the code before you try a new run. If you don’t, the execution time could be warped, which might wrongly influence your decisions.
Up next: How to open a file and transfer data to the target file. (Blog 3/7)
Did reading this blog make you hungry for more Excel or Power BI? Be sure to check out our open Excel courses. See you soon in Antwerp or Brussels!
