How to Transpose Rows to Columns Using Excel VBA (4 Examples)

Dataset Overview

To illustrate the process of transposing rows to columns in Excel using VBA, we’ll work with a dataset representing the marks obtained by 5 students out of 100 in 3 subjects.

Example 1 – Using the VBA Transpose Function

Open the VBA window by pressing Alt + F11.
Insert a new module (Insert ➨ Module).

Use Transpose Function in Excel VBA to Transpose Rows to Columns

Enter the following code to the module:

Sub Transpose_Function()
Range("G4:L7").Value = WorksheetFunction.Transpose(Range("B4:E9"))
End Sub

Return to the sheet.

Use Transpose Function in Excel VBA to Transpose Rows to Columns

Go to Developer and select Macros.

Use Transpose Function in Excel VBA to Transpose Rows to Columns

Select the specified macro name and click Run.

The transposed data will appear in the destination range G4:L7. Note that this macro does not retain formatting.

Example 2 – Using the VBA PasteSpecial

Follow the first two steps from Example 1 to open the VBA window and insert a new module.
Add the following code:

Sub Transpose_PasteSpecial()
Range("B4:E9").Copy
Range("G4").PasteSpecial Transpose:=True
End Sub

Return to your sheet.

Use Paste Special in Excel VBA to Transpose Rows to Columns

Open the Macros dialog box (as in Example 1) and run the macro.

Use Paste Special in Excel VBA to Transpose Rows to Columns

The output will include formats in the destination range.

Read More: How to Transpose in Excel VBA

Example 3 – Transposing Rows to Columns with Defined Sheet and Range

Follow the steps from Example 1 to open the VBA window and insert a new module.
Insert the following code:

Sub Transpose_DefinedSheetnRange()
Sheets("Defined Range").Range("G4:L7").Value = WorksheetFunction.Transpose(Range("B4:E9"))
End Sub

Return to your sheet.

Transpose Rows to Columns with Defined Sheet and Range Using VBA

Open the Macros dialog box and run the macro.

Transpose Rows to Columns with Defined Sheet and Range Using VBA

The data will be transposed, but formatting won’t be preserved.

Example 4 – Transposing Rows to Columns with User Selection

Open the VBA window and insert a new module (as in Example 1).
Enter the following code:

Sub Transpose_withSelection()
Dim sRange As Range
Dim dRange As Range
Set sRange = Application.InputBox(Prompt:="Select the transposing range", _
Title:="VBA for Transposing Rows to Columns", Type:=8)
Set dRange = Application.InputBox(Prompt:="Choose an upper-left cell for the destination range", _
Title:="VBA for Transposing Rows to Columns", Type:=8)
sRange.Copy
dRange.Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
End Sub