VBA Transpose Array

Written by

Editorial Team

Reviewed by

Steve Rynearson

Last updated on September 18, 2022

This tutorial will teach you how to transpose an array using VBA.

Transpose Array

This function will Transpose a 2-dimensional array:

Function TransposeArray(MyArray As Variant) As Variant
    Dim x As Long, y As Long
    Dim maxX As Long, minX As Long
    Dim maxY As Long, minY As Long
    
    Dim tempArr As Variant
    
    'Get Upper and Lower Bounds
    maxX = UBound(MyArray, 1)
    minX = LBound(MyArray, 1)
    maxY = UBound(MyArray, 2)
    minY = LBound(MyArray, 2)
    
    'Create New Temp Array
    ReDim tempArr(minY To maxY, minX To maxX)
    
    'Transpose the Array
    For x = minX To maxX
        For y = minY To maxY
            tempArr(y, x) = MyArray(x, y)
        Next y
    Next x
    
    'Output Array
    TransposeArray = tempArr
    
End Function

Sub TestTransposeArray()
    Dim testArr(1 To 3, 1 To 2) As Variant
    Dim outputArr As Variant
    
    'Assign Array Values
    testArr(1, 1) = "Steve"
    testArr(1, 2) = "Johnson"
    testArr(2, 1) = "Ryan"
    testArr(2, 2) = "Johnson"
    testArr(3, 1) = "Andrew"
    testArr(3, 2) = "Scott"
    
    'Call Transpose Function
    outputArr = TransposeArray(testArr)
    
    'Test Output
    MsgBox outputArr(2, 1)

End Sub

To test this function, call the procedure TestTransposeArray: here an initial array testArr is created and outputArr is the final transposed array.

WorksheetFunction.Transpose

Instead, you might want to transpose an array to Excel. To do so, you can use the Excel Transpose Worksheet Function.

This procedure will transpose a 2D array to an Excel range using the Transpose Worksheet Function:

Sub TestTransposeArray_Worksheetfx()
    Dim maxX As Long, minX As Long
    Dim maxY As Long, minY As Long
    
    'Create Array and Assign Values
    Dim MyArray(1 To 3, 1 To 2) As Variant
    
    MyArray(1, 1) = "Steve"
    MyArray(1, 2) = "Johnson"
    MyArray(2, 1) = "Ryan"
    MyArray(2, 2) = "Johnson"
    MyArray(3, 1) = "Andrew"
    MyArray(3, 2) = "Scott"
    
    'Get Upper and Lower Bounds
    maxX = UBound(MyArray, 1)
    minX = LBound(MyArray, 1)
    maxY = UBound(MyArray, 2)
    minY = LBound(MyArray, 2)
    
    'Transpose Array to Excel
    Range("a1").Resize(maxY - minY + 1, maxX - minX + 1).Value = _
      Application.WorksheetFunction.Transpose(MyArray)

End Sub

 

 

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users! vba save as


Learn More!
vba-free-addin

VBA Code Examples Add-in

Easily access all of the code examples found on our site.

Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.

(No installation required!)

Free Download

Return to VBA Code Examples