Excel VBA: Transpose Multiple Rows in Group to Columns

In Microsoft Excel, you may face various situations where you need to transpose multiple rows to columns. Now, you can also do this with the manual process. But, a simple VBA code can reduce your workload and time with ease. If you want to transpose multiple rows in a group to columns using VBA in Excel, you have come to the right place. This tutorial will demonstrate to you exactly that.

This tutorial will be on point with suitable examples and proper illustrations. So, read the whole article to develop your Excel knowledge.


Download Practice Workbook

Download the practice workbook and practice while reading the article.


Step-by-Step Procedure to Transpose Multiple Rows in Group to Columns with Excel VBA

In the following section, we will provide you with a complete guideline to build a VBA code that will transpose multiple rows in a group to columns in Excel. We recommend you learn and apply all these methods to your dataset. It will surely develop your Excel knowledge.

Let us introduce you to our dataset first.

excel transpose multiple rows in group to columns vba

We will use this dataset to demonstrate this article. Here, we have a simple dataset of some salespersons and their selling details. We will convert these multiple rows to columns using VBA in Excel. Let’s get into it.


1. Build the Code

Follow these steps to build this VBA code.

Step 1: Open the VBA Editor

First, press Alt+F11 on your keyboard. Then, click on Insert > Module.


Step 2: Create Subprocedure

Sub tranpose_multiple_rows_to_columns()

End Sub

This is our subprocedure. We will write rest of our codes here.


Step 3: Declare Necessary Variables

Sub tranpose_multiple_rows_to_columns()

    Dim source As Range
    Dim destination As Range

End Sub

These will be our variables.

source: This variable will store the dataset range.

destination: It will store a cell where we will paste our resulting dataset.


Step 4: Take User Input for the Rows

Sub tranpose_multiple_rows_to_columns()

    Dim source As Range
    Dim destination As Range

    Set source = Application.InputBox("Select the Rows", Type:=8)

End Sub

Application.InputBox(“Select the Rows”, Type:=8): It will show you a input box like the following:

Here, Type:=8 means, it will take Range as input.


Step 5: Take User Input for a Cell Where You Want to Paste Result

Sub tranpose_multiple_rows_to_columns()

    Dim source As Range
    Dim destination As Range

    Set source = Application.InputBox("Select the Rows", Type:=8)
    Set destination = Application.InputBox("Select a Cell to Paste Result", Type:=8)

End Sub

Application.InputBox(“Select a Cell to Paste Result”, Type:=8): It will show you a input box like the following:

Basically, it will ask you to give a cell value where you want to paste your result.

Here, Type:=8 means, it will take Range as input.


Step 6: Copy the Dataset

Sub tranpose_multiple_rows_to_columns()

    Dim source As Range
    Dim destination As Range

    Set source = Application.InputBox("Select the Rows", Type:=8)
    Set destination = Application.InputBox("Select a Cell to Paste Result", Type:=8)

    Application.ScreenUpdating = False
    source.Copy

End Sub

source.Copy: It will copy your given range.
Application.ScreenUpdating = False: We set this to False to disable screen updating. Your code will run in the background. This will make our VBA code faster.


Step 7: Select Destination

Sub tranpose_multiple_rows_to_columns()

    Dim source As Range
    Dim destination As Range

    Set source = Application.InputBox("Select the Rows", Type:=8)
    Set destination = Application.InputBox("Select a Cell to Paste Result", Type:=8)

    Application.ScreenUpdating = False
    source.Copy
    destination.Select

End Sub

destination.Select: It will select the cell that you have given in the second input box.


Step 8: Paste and Transpose Multiple Rows to Columns

Sub tranpose_multiple_rows_to_columns()

    Dim source As Range
    Dim destination As Range

    Set source = Application.InputBox("Select the Rows", Type:=8)
    Set destination = Application.InputBox("Select a Cell to Paste Result", Type:=8)

    Application.ScreenUpdating = False
    source.Copy
    destination.Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

End Sub

Selection.PasteSpecial: It will paste your output on desired cell.

Paste:=xlPasteAll: It will paste everything.

SkipBlanks:=False: It will also paste blank cells if there are any.

Transpose:=True: Finally, it will transpose those multiple rows in the group to columns.


Step 9: Clear the Clipboard (Final Code)

Sub tranpose_multiple_rows_to_columns()

    Dim source As Range
    Dim destination As Range

    Set source = Application.InputBox("Select the Rows", Type:=8)
    Set destination = Application.InputBox("Select a Cell to Paste Result", Type:=8)

    Application.ScreenUpdating = False
    source.Copy
    destination.Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    Application.CutCopyMode = False
    Application.ScreenUpdating = True

End Sub

Application.CutCopyMode = False: It will clear your clipboard. Basically, when we cut or copy some range of cells, it is stored to the clipboard of Excel. And you can paste it multiple times. To clear the memory, we set this property to False.

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


Similar Readings


2. Run the Code

We have built our code. Now it’s time to run our code. Follow these steps to run the code:

📌 Steps

  • First, press Alt+F8 to open the Macro dialog box.
  • Then, select tranpose_multiple_rows_to_columns.
  • After that, click on Run.
  • Now, select the range of cells that you want to transpose.

excel transpose multiple rows in group to columns vba

  • Next, select the cell where you want to paste the result.

excel transpose multiple rows in group to columns vba

  • Finally, click on OK.

excel transpose multiple rows in group to columns vba

As you can see, our VBA code worked perfectly to transpose multiple rows in a group to columns in Excel with ease.

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


💬 Things to Remember

✎ You can also use this VBA code to transpose multiple columns to rows in Excel.

✎ You can select a specific portion of your dataset and transpose it through this VBA code.


Conclusion

To conclude, I hope this tutorial has provided you with a piece of useful knowledge to transpose multiple rows in group to columns in Excel Using VBA codes. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.

Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.

Keep learning new methods and keep growing!


Related Articles

Shanto

Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

2 Comments
  1. TNX for this lesson.
    I am a first time user of VBA and this is a function I want to learn.

Leave a reply

ExcelDemy
Logo