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

Get FREE Advanced Excel Exercises with Solutions!

For transposing rows to columns in Excel, there are many ways but we can do it smartly with lots of customization using VBA. From this article, we’ll learn 4 useful examples to transpose rows to columns using Excel VBA with simple steps and clear images.


Download Practice Workbook

You can download the free Excel template from here and practice on your own.


4 Examples to Transpose Rows to Columns Using Excel VBA

To demonstrate the examples, we’ll use the following dataset that represents 5 students’ obtained marks out of 100 in 3 subjects.


1. Use VBA Transpose Function in Excel to Transpose Rows to Columns

In our very first example, we’ll use the Transpose function of VBA to transpose rows to columns. Here, we’ll need to set the source range and destination range in the codes.

Steps:

  • Press Alt + F11 to open the VBA window.
  • Then click as follows to insert a module: Insert ➨ Module.

Use Transpose Function in Excel VBA to Transpose Rows to Columns

  • Then type the following codes in the module-
Sub Transpose_Function()
Range("G4:L7").Value = WorksheetFunction.Transpose(Range("B4:E9"))
End Sub
  • After that, go back to the sheet.

Use Transpose Function in Excel VBA to Transpose Rows to Columns

  • Now, click as follows: Developer ➨ Macros.

Use Transpose Function in Excel VBA to Transpose Rows to Columns

  • After appearing the Macros dialog box, select the specified Macro name and press Run.

Here’s the transposed data in the destination range G4:L7. This macro can’t keep the formats.

Read More: Excel VBA: Transpose Multiple Rows in Group to Columns


2. Use of VBA PasteSpecial in Excel to Transpose Rows to Columns

Now we’ll use the Paste Special method in Excel VBA to do the task. This macro will also need the defined range. It will be able to keep the whole format.

Steps:

Sub Transpose_PasteSpecial()
Range("B4:E9").Copy
Range("G4").PasteSpecial Transpose:=True
End Sub
  • Then go back to your sheet.

Use Paste Special in Excel VBA to Transpose Rows to Columns

Use Paste Special in Excel VBA to Transpose Rows to Columns

Soon after, you will get the output including formats in the destination range like the image below.

Read More: How to Transpose in Excel VBA (3 Methods)


Similar Readings


3. Transpose Rows to Columns with Defined Sheet and Range Using VBA

The previous Macros were working in the active sheet. In this example, we’ll use a Macro that will work on a defined sheet and defined range. But it won’t be able to keep the formats.

Steps:

Sub Transpose_DefinedSheetnRange()
Sheets("Defined Range").Range("G4:L7").Value = WorksheetFunction.Transpose(Range("B4:E9"))
End Sub
  • After that go back to your sheet.

Transpose Rows to Columns with Defined Sheet and Range Using VBA

Transpose Rows to Columns with Defined Sheet and Range Using VBA

Now see, the Macro has transposed the data properly excluding formats.

Read More: VBA to Transpose Multiple Columns into Rows in Excel (2 Methods)


4. Transpose Rows to Columns By Selecting Range After Running the Macro

Here, we’ll transpose rows to columns by using VBA differently. First, we’ll run the Macro then we’ll select the source range and destination range. It is more user-friendly than the previous examples.

Steps:

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
  • Later, go back to the sheet.

Transpose Rows to Columns By Selecting Range After Running the Macro

Code Breakdown

  • First, I created a Sub procedure Transpose_withSelection.
  • Then defined two variables- sRange and dRange as Range.
  • Next, I used InputBox to set the operation name for the input box.
  • And used Title to define the title for the input boxes.
  • Finally, applied PasteSpecial to paste the selected data in transposed form.

Soon after, you will get an input box to select the source range.

Transpose Rows to Columns By Selecting Range After Running the Macro

  • Select the source range and press OK.

Transpose Rows to Columns By Selecting Range After Running the Macro

Next, you will get another Input box to set the cell from where the output will start.

  • Select the cell and press OK.

Then you will get the transposed data including the formats as shown in the image below.

Read More: VBA to Transpose Array in Excel (3 Methods)


Conclusion

I hope the procedures described above will be good enough to transpose rows to columns using Excel VBA. Feel free to ask any question in the comment section and please give me feedback.


Related Articles

 

Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo