How to Transpose an Array with VBA in Excel (3 Examples)

When we work with data in Excel, there are times when rows need to become columns and columns need to become rows; this is called transposing. While Excel provides a built-in TRANSPOSE function for worksheet use, many professionals prefer this process using VBA, particularly when we deal with large datasets or dynamic ranges.

The Transpose function in VBA helps to flip a dataset from rows to columns (or vice versa). It is best used when we want to transfer data from one sheet area to another or when manipulating arrays in memory. Works well with structured tabular data like monthly reports, sales figures, etc.

We have taken a dataset that represents monthly salaries of two employees over four months. If we use VBA Transpose, the structure is flipped so each employee gets a full row for horizontal analysis.

Steps:

➤ Open your dataset. We have a range A1:C5 with months in column A and employees in columns B to C.

➤ Open the Excel workbook, press  Alt  +  F11  to open the VBA Editor window. Go to Insert > Module.

➤ A blank code window will open. Paste the following VBA code in the module window. Then press  F5  .

Sub TransposeSimpleData()
Dim srcRange As Range
Dim destRange As Range
Dim result As Variant
Set srcRange = Sheets("Sheet1").Range("A1:C5")
result = Application.Transpose(srcRange.Value)
Set destRange = Sheets("Sheet1").Range("E1")
destRange.Resize(UBound(result, 1), UBound(result, 2)).Value = result
End Sub

➤ Previewed after the transposed table in range E1:I3. The data now appears transposed, with employee names in rows and months across columns.

This method uses VBA to copy a selected range and paste it transposed data by the help of Excel’s built-in PasteSpecial feature. It is good for users who want to transpose cell values from a range without any changes with arrays. Best used for short tables directly on the sheet.

We have taken a dataset that represents monthly salaries of two employees over four months. If we use VBA Transpose, the structure is flipped so each employee gets a full row for horizontal analysis.

Steps:

➤ Open your dataset. We have a range A1:D3 with months in Row 1 and employees in Row 2 to 3.

➤ Open the Excel workbook, press  Alt  +  F11  to open the VBA Editor window. Go to Insert > Module.

➤ A blank code window will open. Paste the following VBA code in the module window. Then press  F5  .

Sub TransposeSmallTable()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet2")
ws.Range("A1:D3").Copy
ws.Range("F1").PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End Sub

➤ Previewed after the transposed table in range F1:H4. The data now appears transposed, with employee names in columns and months across columns.

This method uses a custom VBA function to manually transpose a two-dimensional array. It is useful when working with large or sensitive datasets that might exceed the limits of Application.Transpose, or contain values like long text or Nulls that the built-in function cannot handle. You will use this in memory (array-to-array), and optionally output it to a worksheet.

We have taken a dataset that represents students and the subjects like Math, Science and English. If we want to flip the layout so subjects become rows and students become columns it is helpful for subject-wise performance comparison across all students.

Steps:

➤ Open your dataset. We have a range from A1:D4 with Students name in Column A and Subjects in Row 1.

➤ Open the Excel workbook, press  Alt  +  F11  to open the VBA Editor window. Go to Insert > Module.

➤ A blank code window will open. Paste the following VBA transpose function in the module window.

Function ManualTranspose(arr As Variant) As Variant
    Dim i As Long, j As Long
    Dim rows As Long, cols As Long
    Dim result() As Variant
    rows = UBound(arr, 1)
    cols = UBound(arr, 2)
    ReDim result(1 To cols, 1 To rows)
    For i = 1 To rows
        For j = 1 To cols
            result(j, i) = arr(i, j)
        Next j
    Next i
    ManualTranspose = result
End Function
Sub TransposeScores()
    Dim inputRange As Range
    Dim inputData As Variant
    Dim transposedData As Variant
    Set inputRange = Sheets("Sheet3").Range("A1:D4")
    inputData = inputRange.Value
    transposedData = ManualTranspose(inputData)
    Sheets("Sheet3").Range("F1").Resize(UBound(transposedData, 1), UBound(transposedData, 2)).Value = transposedData
End Sub

This function takes a 2D array, flips the rows and columns using nested loops, and returns the transposed version.

➤ Press  F5  or go to Run > Run Sub/UserForm to execute the TransposeScores macro.

➤ After applying manual VBA transpose, the flipped table will now appear in range F1:I4 on the same worksheet.

Frequently Asked Questions

How to assign values to an array in Excel VBA?

You can assign values using either a loop or a static array declaration:

Dim arr(1 To 3) As Integer
arr(1) = 10: arr(2) = 20: arr(3) = 30

How to transpose data in Excel using VBA?

Use the Application.WorksheetFunction.Transpose method to convert rows to columns or columns to rows programmatically.

How do you transpose an array in Excel?

Declare a new array variable and assign the transposed version using:

transposedArray = Application.WorksheetFunction.Transpose(originalArray)

How do I assign a value into an array?

Specify the index and assign:

vba
CopyEdit
myArray(0) = “Value”

Concluding Words

The Application.Transpose method is the most efficient way for standard cases, while the PasteSpecial Transpose method and custom VBA transposition functions offer alternatives when limits are encountered. We will choose right VBA formula to handle transpositions becomes seamless, particularly for automation and reporting tasks.