How to Transpose in Excel VBA (3 Easy Ways)

One of the most important and widely used activities that we have to accomplish while working in Excel is to transpose a data set. Today I’ll show you how you can transpose both one-dimensional and multi-dimensional arrays in Excel using VBA.

Download Practice Workbook

You may download the following Excel workbook for better understanding and practice it by yourself.

3 Handy Approaches to Transpose in Excel VBA

Here we’ve got a data set with the Names of some products and their Sales in three years of a company called Rainbow Group.

Today our objective is to transpose this data set using VBA in Excel.

Method 1: Applying VBA to Transpose One Dimensional Array in Excel

First of all, we’ll try to transpose a one-dimensional array through VBA. VBA is a programming language that may be used for a variety of tasks, and different types of users can use it for those tasks. Using the Alt + F11 keyboard shortcut, you can launch the VBA editor. In the last section, we will generate VBA code that makes it very easy to fill down to the last row with data Excel.

Steps:

• Firstly, we will open the Developer tab.
• Then, we will select the Visual Basic command.

• Here, the Visual Basic window will open.
• After that, from the Insert option, we will choose the new Module to write the VBA code.

• Therefore, a new module window called “Module 1” will open. 
• Besides, insert and save the following VBA code in the module.

Sub Transpose()
Dim Destination As String
Destination = InputBox("Enter the Range Where You Want to Keep the Transposed Array: ")
Range(Destination).Value = WorksheetFunction.Transpose(Range(Selection.Address))
End Sub

• Then, Return to your workbook and select the one-dimensional array that you want to transpose.
• Here I am selecting the Product Name column.

• Afterward, press ALT+F8 on your keyboard.
• Now, a dialogue box called Macro will open. Select Transpose and click on Run.

• You will get an input box that will ask you to enter the cell reference of the range where you want to keep the transposed array.
• Enter the cell reference of the range where you want to keep the transposed array. Here I have entered H4:R4.
• Then click OK.

• Finally, You will get the transpose of your selected array in your selected destination.

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

Similar Readings

• How to Transpose Duplicate Rows to Columns in Excel (4 Ways)
• 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)

Method 2: Using VBA to Transpose Multi-Dimensional Array in Excel

The VBA code described above works for multi-dimensional arrays too.

Therefore, instead of transposing a single column, you can transpose the whole data set using this code.

Steps:

• Firstly, insert and save the following VBA Macro in module 1.

Sub Transpose()
Dim Destination As String
Destination = InputBox("Enter the Range Where You Want to Keep the Transposed Array: ")
Range(Destination).Value = WorksheetFunction.Transpose(Range(Selection.Address))
End Sub

• Then select the whole data set instead of a single column.

• Afterward, press ALT+F8 on your keyboard.
• Now, a dialogue box called Macro will open. Select Transpose and click on Run.

• This time I have entered range H4:R8 to keep the transposed range.
• Then click OK.

• Finally, you will find your whole data set being transposed in your destination cell range.

Read More: Conditional Transpose in Excel (2 Examples)

Method 3: Employing VBA Paste Special Method to Transpose with Keeping the Format Intact

The VBA code described above does transpose the whole data set, but it doesn’t copy the format along with it.

That means, it just copies the values but doesn’t copy the design and other formats that we applied to our main data set for the purpose of beautification.

But you can use a slightly changed VBA code to transpose your data set along with keeping the format intact.

Steps:

• Firstly, insert and save the following VBA Macro into module 2.

Sub Transpose_Paste_Value()
Dim sourceRng As Excel.Range
Dim taretRng As Excel.Range
Set sourceRng = Range(Selection.Address)
Set targetRng = Range(InputBox("Enter the Range Where You Want to Keep the Transposed Array: "))
sourceRng.Copy
targetRng.PasteSpecial Operation:=xlNone, SkipBlanks:=False, Transpose:=True
End Sub

• Then, Return to your workbook and select the entire data set.

• Afterward, press ALT+F8 on your keyboard.
• Now, a dialogue box called Macro will open. Select Transpose_Paste_Value and click on Run.

• After that, you will get an input box that will ask you to enter the cell reference of the range where you want to keep the transposed array.
• Besides, enter the cell reference of the range where you want to keep the transposed array. Here I have entered H4:R8.
• Then, click OK.

• Therefore, the transposed array of your data set will be created in your selected range, carrying the format from the original data set.

Read More: Excel Paste Transpose Shortcut: 4 Easy Ways to Use

Conclusion

Using these methods, you can transpose a data set of any dimension in your workbook taking the help of Excel VBA. Do you know any other method? Or do you have any questions? Feel free to ask us.

Related Articles

• How to Transpose Every n Rows to Columns in Excel (2 Easy Methods)
• Excel VBA: Transpose Multiple Rows in Group to Columns
• How to Transpose Rows to Columns Based on Criteria in Excel
• Transpose Rows to Columns Using Excel VBA (4 Ideal Examples)
• How to Transpose a Table in Excel (5 Suitable Methods)
• How to Transpose Rows to Columns in Excel (5 Useful Methods)
• How to Reverse Transpose in Excel (3 Simple Methods)