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!