How to Transpose in Excel VBA (3 Methods)

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

3 Suitable Methods 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.

Read more: How To Transpose Data in Excel

1. Transpose One Dimensional Array in Excel VBA

First of all, we’ll try to transpose a one-dimensional array through VBA.

Step 1:

➤ First, press ALT+F11 on your keyboard. The VBA window will open.

Step 2:

➤ Go to the Insert tab in the VBA window. 

➤ Then from the options available, select Module.

Step 3:

➤ A new module window called “Module 1” will open. 

➤ Insert the following VBA code in the module.

♦ Code:

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

Step 4:

➤ Save the workbook as Excel Macro-Enabled Workbook.

Step 5:

➤ Return to your workbook and select the one-dimensional array that you want to transpose.

➤ Here I am selecting the Product Name column.

Step 6:

➤ Press ALT+F8 on your keyboard.

➤ A dialogue box called Macro will open. Select Transpose and click on Run.

Step 7:

➤ 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 H3:R3.

Step 8:

➤ Then click OK.

➤ You will get the transpose of your selected array in your selected destination.

2. Transpose Multi-Dimensional Array in Excel VBA

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.

Step 1:

➤ Follow steps 1-4 of the previous method to insert the code and save the workbook.

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

Step 2:

➤ Follow steps 6-7 from the previous method to run the Macro.

➤ This time I have entered range H3:R7 to keep the transposed range.

Step 3:

➤ Then click OK. You will find your whole data set being transposed in your destination cell range.

3. Transpose in Excel VBA with Paste Special Method (For 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 on 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.

First, run steps 1-4 from method 1 to save the Macro. Just enter the following VBA code in place of the code.

♦ Code:

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 follow steps 5-7 from method-1 to run the Macro (This time the name of the Macro is Transpose_Paste_Value.)

The transpose array of your data set will be created in your selected range, carrying the format from the original data set.

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 Readings

• Transpose a Table in Excel (5 Suitable Methods)
• How to Transpose in Excel (5 Easy Ways)
• How to Transpose Rows to Columns in Excel (5 Useful Methods)
• Conditional Transpose in Excel (2 Examples)
• How to Transpose Array in Excel (3 Simple Ways)
• How to Transpose Columns to Rows In Excel (6 Most Effective Methods with Pictures)