Tip 1: Copy cells with VBA
This is the first in a series of 7 blog posts that delve deeper into how you can speed up data transformation in Excel VBA. In this blog post, you’ll learn how to save time by making certain cells equal to others instead of copy-pasting separate cells/ranges.
- 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
The task of copy-pasting data is carried out by the operating system (Windows), which means Excel needs to communicate with an external application for each action. If you only do it once, you won’t notice any inconvenience, but if you need to loop copy-paste actions, their execution time will build up. You can bypass this by making pairs of cells equal: this way, Excel will only need to exchange data between two of its own building blocks. It’s a lightning-fast action that’s efficient even when it’s repeated countless times.
In this blog post, we’ll start with a data matrix consisting of 10,000 rows and 5 columns. Our goal is to copy the values in all rows into a new vertical data region. The data on each row should be listed in turn under the first. The result should be a vertical list of 50,000 cells in 1 column, as illustrated below.
In Excel, you can do this manually by copying the first row (green) and selecting ‘Transpose’ under ‘Paste Special’.
If you carry out this action in VBA, the result will be as follows:
Range(“A1:E1”).Select
Selection.Copy
Range(“G1”).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Here’s a cleaned-up version of the code:
Range(“A1:E1”).Copy
Range(“G1”).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Of course, you could just repeat this action 10,000 times to handle every row in the series – but in the next steps, I’ll show you a more efficient approach in VBA. You can see the indicative execution time in each example, so you’ll notice that every adjustment of the VBA code yields quicker results.
Because copy-pasting data is handled by the operating system (Windows), you’d need to communicate with an external application 10,000 times in this example. Copy-pasting one cell or range only takes a fraction of a second. Copying 5 cells takes about as long as copying 10,000 cells at once – but if you ask the system 10,000 times to copy 5 cells, the execution time gets multiplied by 10,000.
In the example below, the recorded macro is executed 10,000 times. I’ve also included some variables to handle the row/column counters more efficiently. The execution time was established by running the code on my laptop; it’s only meant to demonstrate how the example codes compare to each other. Your own PC’s execution time might be different, but the proportions should be similar.
Sub Ex1_CopyTranspose()
Dim xRow As Long ‘ RowCounter for row 1 till 10000
Dim dRow As Long ‘ Destination Row
Debug.Print Now
dRow = 1
For xRow = 1 To 10000 Step 1
Range(Cells(xRow, 1), Cells(xRow, 5)).Copy
Cells(dRow, 7).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
dRow = dRow + 5
Next xRow
Debug.Print Now
‘Code Execution : 06:05 min
End Sub
Note: The code ‘Debug.print Now’ shows the start and end time of the execution. To see the result, open the ‘Immediate Window’ (Debug Window) in Visual Basic using the key combination ‘CTRL + G’.
In this first example, Excel would need over 6 minutes to paste all rows in a separate column.
In the following example, we’ll add two known lines of code that order Excel to refrain from updating the screen during the execution; instead, Excel will wait until the process is completed. This greatly improves the execution speed of your VBA code. Why did I only add this now? You’ll see in the next examples.
You’ll need to use Application.ScreenUpdating = False at the start of a (piece of) code and Application.ScreenUpdating = True at the end of a (piece of) code.
Sub Ex2_CopyTranspose()
Dim xRow As Long ‘
Dim dRow As Long ‘ Destination Row
Debug.Print Now
Application.ScreenUpdating = False
dRow = 1
For xRow = 1 To 10000 Step 1
Range(Cells(xRow, 1), Cells(xRow, 5)).Copy
Cells(dRow, 7).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
dRow = dRow + 5
Next xRow
Application.ScreenUpdating = True
Debug.Print Now
‘Code Execution : 01:11 min
End Sub
Important: This code works at the application level. If you forget to turn ‘refresh’ back on (True) in the code, Excel will stop executing changes on the screen. You can also execute the text ‘Application.ScreenUpdating = True’ from the Direct Window (CTRL + G in VBA).
As you see, adding ‘ScreenUpdating’ saves us quite some time: the code’s execution time has been reduced from 6:05 minutes to only 1:11 minute. But we still need to communicate with the clipboard 10,000 times.
What if we were to ask Excel to make the relevant cells equal? Cell G1 equals cell A1, G2 equals A2, etc. until all 50,000 cells have been copied to the correct spot. This method eliminates the need to communicate with the external clipboard – you’re working exclusively with Excel’s own building blocks. As you can imagine, this saves a considerable amount of time.
Note: Another advantage of this method is that only the cell’s content is pasted into the other cell , not the formatting.
This time, we’ll add extra variables to introduce a loop from left to right for every row (column 1 to 5).
Sub Ex3_CopyTranspose()
Dim xRow As Long ‘ 1 to 10000 (Top Bottom)
Dim xCol As Long ‘ 1 to 5 (Left Right)
Dim dRow As Long ‘ Destination Row
Debug.Print Now
dRow = 1
For xRow = 1 To 10000 Step 1
For xCol = 1 To 5
Cells(dRow, 7) = Cells(xRow, xCol)
dRow = dRow + 1
Next xCol
Next xRow
Debug.Print Now
‘Code Execution : 00:02 to 00:03 sec
End Sub
The execution time of this code is somewhere between 2 and 3 seconds. You can still add ‘ScreenUpdating’, but it won’t make the operation faster; I’ve tested it with 20,000 rows and the execution time stayed the same. That said, I do suggest including these lines in the code, because it might make a difference on slower computers.
Sub Ex4_CopyTranspose()
Dim xRow As Long ‘ 1 to 10000 (Top Bottom)
Dim xCol As Long ‘ 1 to 5 (Left Right)
Dim dRow As Long ‘ Destination Row
Debug.Print Now
Application.ScreenUpdating = False
dRow = 1
For xRow = 1 To 10000 Step 1
For xCol = 1 To 5
Cells(dRow, 7) = Cells(xRow, xCol)
dRow = dRow + 1
Next xCol
Next xRow
Application.ScreenUpdating = True
Debug.Print Now
‘Code Execution : 00:02 to 00:03 sec
End Sub
Next blog: Add data to a table (Blog 2/7)
Feeling inspired to follow an Excel or Power BI course? Be sure to check out our open Excel courses. See you soon in Antwerp or Brussels!