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

Sometimes we need to re-organize columns into rows when working on a large dataset in Excel for better readability. Implementing VBA is the most effective, quickest, and safest method to run any operation in Excel. In this article, we will show you how to transpose multiple columns into rows in Excel with the VBA macro.


Download Workbook

You can download the free practice Excel workbook from here.


2 Methods of VBA to Transpose Multiple Columns into Rows in Excel

In this section, we will show how to transpose multiple columns into multiple rows and multiple columns into a single row in Excel with VBA code.

The given dataset below we will be using throughout the whole article as examples.

Dataset of VBA to Transpose Multiple Columns into Rows in Excel

1. Embed VBA to Transpose Multiple Columns into Multiple Rows

The steps to transpose multiple columns into multiple rows with VBA code are given below.

Steps:

  • In the beginning, press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor.

  • In the pop-up code window, from the menu bar, click Insert -> Module.

  • Now, copy the following code and paste it into the code window.
Sub TransposeMultipleColumnsMultipleRows()
    Dim SrcRange As Range
    Dim TrgtRange As Range
    Set SrcRange = Application.InputBox(Prompt:="Please Select the Range You Want to Transpose", Title:="Transpose Columns into Rows", Type:=8)
    Set TrgtRange = Application.InputBox(Prompt:="Please Select the First Cell of the Target Range(one cell only)", Title:="Transpose Columns into Rows", Type:=8)
    SrcRange.Copy
    TrgtRange.Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    Application.CutCopyMode = False
End Sub

Your code is now ready to run.

VBA to Transpose Multiple Columns into Multiple Rows in Excel

  • Then, press F5 on your keyboard or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Play icon in the sub-menu bar to run the macro.

  • This time, a pop-up Input Box will appear asking for the range to transpose.
  • Select and drag the source range to transpose (in our case, it is range B4:F9).
  • Next, click OK.

Selecting Range to Transpose Multiple Columns into Multiple Rows in Excel with VBA

  • Again, a second pop-up Input Box will appear and this time it will ask you for the destination range. You must select only one cell, don’t select a range of cells. This code will consider the selected cell as the first cell of the destination range and paste the columns starting from that cell (in our case, it is Cell B11).
  • Lastly, click OK.

As a result, the multiple columns in your dataset will be now transposed into multiple rows in the new destination of your Excel spreadsheet.

Result of VBA to Transpose Multiple Columns into Multiple Rows in Excel

Macro Code Breakdown

Dim SrcRange As Range
Dim TrgtRange As Range

Declaring two variables as Range type.

Set SrcRange = Application.InputBox(Prompt:="Please Select the Range You Want to Transpose", Title:="Transpose Columns into Rows", Type:=8)
Set TrgtRange = Application.InputBox(Prompt:="Please Select the First Cell of the Target Range(one cell only)", Title:="Transpose Columns into Rows", Type:=8)

Assigning the two declared variables to references. The user will select the references after executing the code. Also provided the Title and the Prompt message that the pop-up Input Box will hold.

SrcRange.Copy

Copying the source range to transpose.

TrgtRange.Select

Selecting the target cell to paste the copied range.

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

Providing the type of Paste operation will occur:

  • Paste:=xlPasteAll -> Everything will be pasted from the selection.
  • Operation:=xlNone -> We are not performing any mathematical operation (such as add, subtract, divide, multiplication etc.), so the operation value will be None.
  • SkipBlanks:=False -> As we are pasting everything so we won’t even skip blank cells.
  • Transpose:=True -> To transpose the selected range.
Application.CutCopyMode = False

Cancelling the Cut or Copy mode and clearing the clipboard.

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


Similar Readings


2. Implement Macro to Transpose Multiple Columns into Single Row

The steps to transpose multiple columns into single rows with VBA macro are given below.

Steps:

  • Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
  • In the code window, copy the following code and paste
Sub TransposeMultipleColumnsSingleRow()
    Dim SrcRange As Range
    Dim TrgtRange As Range
    xTitleId = "Transpose Multiple Columns into Single Row"
    Set SrcRange = Application.Selection
    Set SrcRange = Application.InputBox("Please Select the Range You Want to Transpose", xTitleId, SrcRange.Address, Type:=8)
    Set TrgtRange = Application.InputBox("Please Select the First Cell of the Target Range(one cell only)", xTitleId, Type:=8)
    Application.ScreenUpdating = False
    iRows = SrcRange.Rows.Count
    iColumns = SrcRange.Columns.Count
    For i = 1 To iRows
        SrcRange.Rows(i).Copy TrgtRange
        Set TrgtRange = TrgtRange.Offset(0, iColumns + 0)
    Next
    Application.ScreenUpdating = True
End Sub

Your code is now ready to run.

VBA to Transpose Multiple Columns into Single Rows in Excel

  • Next, Run the code as shown above and a pop-up Input Box will appear asking for the range to transpose.
  • Select and drag the source range to transpose (in our case, it is range B4:F9).
  • Next, click OK.

Selecting Range to Transpose Multiple Columns into Single Rows in Excel with VBA

  • Again, a second pop-up Input Box will appear and this time it will ask you for the destination range. You must select only one cell, don’t select a range of cells. This code will consider the selected cell as the first cell of the destination range and paste the columns starting from that cell (in our case, it is Cell B11).
  • Lastly, click OK.

As a result, the multiple columns in your dataset will be now transposed into a single row in the new destination of your Excel spreadsheet.

Result of VBA to Transpose Multiple Columns into Single Rows in Excel

VBA Code Breakdown

Dim SrcRange As Range
Dim TrgtRange As Range

Declaring two variables as Range type.

xTitleId = "Transpose Multiple Columns into Single Row"

Providing the Title that the pop-up Input Box will hold while performing the operation.

Set SrcRange = Application.Selection
Set SrcRange = Application.InputBox("Please Select the Range You Want to Transpose", xTitleId, SrcRange.Address, Type:=8)
Set TrgtRange = Application.InputBox("Please Select the First Cell of the Target Range(one cell only)", xTitleId, Type:=8)

Assigning the two declared variables to references. The user will select the references after executing the code. Also passing the Title that was declared above.

Application.ScreenUpdating = False

Turning off the screen updating option to speed up the macro.

iRows = SrcRange.Rows.Count
iColumns = SrcRange.Columns.Count

Counting the number of rows and columns of the dataset.

For i = 1 To iRows
    SrcRange.Rows(i).Copy TrgtRange
    Set TrgtRange = TrgtRange.Offset(0, iColumns + 0)
Next

Finally, run a loop that will scan through the range given, copy each value and paste them into new cells. The loop process will keep copying and pasting each value and increasing the column reference numbers. It means, every time it takes a value and pastes it one cell, next time while pasting the next value, it pastes it in the same row but in the next column.

Application.ScreenUpdating = True

Turning on the screen updating option.

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


Conclusion

To conclude, this article showed you how to transpose multiple columns into rows in Excel with VBA. I hope this article has been very beneficial to you. Feel free to ask if you have any questions regarding the topic.


Related Articles

Sanjida Ahmed

Sanjida Ahmed

Hello World! This is Sanjida, an Engineer who is passionate about researching real-world problems and inventing solutions that haven’t been discovered yet. Here, I try to deliver the results with explanations of Excel-related problems, where most of my interpretations will be provided to you in the form of Visual Basic for Applications (VBA) programming language. Being a programmer and a constant solution seeker, made me interested in assisting the world with top-notch innovations and evaluations of data analysis.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo