Excel VBA to Transpose Array (3 Suitable Examples)

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 an array in Excel with the VBA macro.

Download Practice Workbook

You can download the free practice Excel template from here.

3 Methods to Transpose Array in Excel using VBA

The generic syntax of transposing Array with VBA is,

Here,

Arg1 = It is a required argument which is a range of cells that will be transposed.

Below in this section, we will discuss how to transpose a one-dimensional and two-dimensional array in Excel. We will also learn how to transpose an array with Paste Special Method in Excel.

1. VBA to Transpose One-Dimensional Array in Excel

Consider the following dataset of a one-dimensional array which we will be using as our example of transposing.

Steps to transpose a one-dimensional array using VBA in Excel are shown below.

Steps:

• 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.

• Copy the following code and paste it into the code window.

Sub Transpose1DArr()
Sheets("1DArr").Range("D4:L4").Value = WorksheetFunction.Transpose(Range("B4:B12"))
End Sub

Your code is now ready to run.

• 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.

Now go back to the worksheet of interest and you will notice that your one-dimensional array, consisting of one column, has successfully transposed as a one-dimensional array of one row.

Code Breakdown:

• Sub Transpose1DArr() -> Initiating a Sub Procedure named Transpose1DArr
• Sheets(“1DArr”).Range(“D4:L4”).Value = WorksheetFunction.Transpose(Range(“B4:B12”))
  • • Sheets(“1DArr”).Range(“D4:L4”).Value -> The Range.Value function is for storing value. We stored the range of our array in the sheet named “1DArr”, in the cell range of “D4:L4”.
    • Transpose(Range(“B4:B12”)) -> The generic VBA syntax of transposing array under the WorksheetFunction object, here we set the range of the array (“B4:B12“) as the argument that we want to transpose.
• End Sub -> Ending the Procedure

Read More: How to Transpose in Excel VBA (3 Methods)

2. VBA to Transpose Two-Dimensional Array in Excel

Now, look at the following dataset of a two-dimensional array and we going to use it as our example of transposing.

Steps to transpose a two-dimensional array using VBA in Excel are shown 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 it.

Sub Transpose2DArr()
Sheets("2DArr").Range("E4:M5").Value = WorksheetFunction.Transpose(Range("B4:C12"))
End Sub

Your code is now ready to run.

• In the same way as before, Run the code and go back to the worksheet of interest. You will notice that your two-dimensional array, consisting of 9 rows and 2 columns, has successfully transposed as a two-dimensional array of 2 rows and 9 columns.

Code Breakdown:

• Sub Transpose2DArr() -> Initiating a Sub Procedure named Transpose2DArr
• Sheets(“2DArr”).Range(“E4:M5”).Value = WorksheetFunction.Transpose(Range(“B4:C12”))
  • • Sheets(“2DArr”).Range(“E4:M5”).Value -> The Range.Value function is for storing value. We stored the range of our array in the sheet named “2DArr”, in the cell range of “E4:M5”.
    • Transpose(Range(“B4:C12”)) -> The generic VBA syntax of transposing array under the WorksheetFunction object, here we set the range of the array (“B4:C12“) as the argument that we want to transpose.

• End Sub -> Ending the Procedure

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

Similar Readings

• Transpose a Table in Excel (5 Suitable Methods)
• How to Transpose Rows to Columns in Excel (5 Useful Methods)
• Transpose Multiple Rows in Group to Columns in Excel
• Transpose Columns to Rows In Excel (6 Methods)
• Calculate the Average of an Array with VBA (Macro, UDF, and UserForm)

3. VBA to Transpose Array with Paste Special Method in Excel

If you want to utilize Excel’s Paste Special method while transposing the array in Excel by VBA, you can do that too. To do that, we need to implement different operations under the Paste Special Method in Excel.
Let us consider the same data as we have used in the previous example.

Steps to transpose array with Paste Special Method using VBA in Excel are shown 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 it.

Sub TransposeArrPasteSpecial()
Dim InputArr As Excel.Range
Dim ResultArr As Excel.Range
Set InputArr = Sheets("PasteSpecialArr").Range("B4:C12")
Set ResultArr = Sheets("PasteSpecialArr").Range("E4:M5")
InputArr.Copy
ResultArr.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
End Sub

Your code is now ready to run.

• In the same way as before, Run the code and go back to the worksheet of interest. You will notice that your two-dimensional array, consisting of 9 rows and 2 columns, has successfully transposed with paste special method as a two-dimensional array of 2 rows and 9 columns.

Code Breakdown:

• Sub TransposeArrPasteSpecial() -> Initiating a Sub Procedure named TransposeArrPasteSpecial
• Dim InputArr As Excel.Range -> Defining a new variable InputArr to store the input data array.
• Dim ResultArr As Excel.Range -> Defining a new variable ResultArr to hold the result array range.
  • • The type of these variables is defined as Range, because we wanted to transpose the data which is an array range.
• Set InputArr = Sheets(“PasteSpecialArr”).Range(“B4:C12”) -> Setting the input array range “B4:C12” from the sheet “PasteSpecialArr” in our defined input array variable (InputArr) using Range function.
• Set ResultArr = Sheets(“PasteSpecialArr”).Range(“E4:M5”) -> Setting the target array range “E4:M5” (where we will store our transposed array) in our defined result array variable (ResultArr) using Range function.
• InputArr.Copy -> Using the command Copy to copy the input array range from the worksheet.
• PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True -> Using the PasteSpecial function on result variable ResultArr to save the transposed result in the target range (E4:M5) in the worksheet.
  • • Paste:=xlPasteValues -> Allows to paste values in different formats like Paste as Values, Formulas, Formats. As we want to paste values in our worksheet so, in our example, we set it as Paste as Values.
    • Operation:=xlNone -> There are various operations that can be performed in Excel like addition, subtraction, multiplication, division etc. As we haven’t used any of those in our code, so we kept it as None.
    • SkipBlanks:=False -> If set False then this command doesn’t skip any blanks from the data. And if set True then this command allows skipping the blanks from the data. We didn’t want to skip any blanks in our data so we set it as False.
    • Transpose:=True -> If set True, then it allows transposing the array of data.
• End Sub -> Ending the Procedure

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

Conclusion

This article showed you how to transpose arrays in Excel using VBA macro. I hope this article has been very beneficial to you. Feel free to ask any questions regarding the topic.

Further Readings

• How to ReDim Preserve 2D Array in Excel VBA (2 Easy Ways)
• Conditional Transpose in Excel (2 Examples)
• How to Transpose a Table in Excel (5 Suitable Methods)
• Excel VBA: Transpose Multiple Rows in Group to Columns
• How to Transpose Rows to Columns Using Excel VBA (4 Ideal Examples)
• VBA to Get Unique Values from Column into Array in Excel (3 Criteria)
• How to Convert Range to Array in Excel VBA (3 Ways)