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.
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
- VBA to Transpose Array in Excel (3 Methods)
- Excel Power Query: Transpose Rows to Columns (Step-by-Step Guide)
- How to Transpose Rows to Columns Using Excel VBA (4 Ideal Examples)
- Transpose Multiple Columns into One Column in Excel (3 Handy Methods)
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.
- Next, select the cell where you want to paste the result.
- Finally, click on OK.
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
- How to Reverse Transpose in Excel (3 Simple Methods)
- How to Transpose Columns to Rows In Excel (6 Methods)
- Convert Columns to Rows in Excel Using Power Query
- How to Convert Columns to Rows in Excel Based On Cell Value
- Conditional Transpose in Excel (2 Examples)
- How to Convert Column to Comma Separated List With Single Quotes
- How to Transpose Array in Excel (3 Simple Ways)
TNX for this lesson.
I am a first time user of VBA and this is a function I want to learn.
Hello, PIET STRYDOM, you can check out this link to learn VBA programming step by step! Thanks!
https://www.exceldemy.com/excel-vba-macros/