How to Transpose a Range in VBA (With Example)

You can use the following basic syntax to transpose a range using VBA:

Sub TransposeRange()

    'specify range to transpose
    MyRange = Range("A1:B5")
    
    'find dimensions of range
    XUpper = UBound(MyRange, 1)
    XLower = LBound(MyRange, 1)
    YUpper = UBound(MyRange, 2)
    YLower = LBound(MyRange, 2)
    
    'transpose range
    Range("D1").Resize(YUpper - YLower + 1, XUpper - XLower + 1).Value = _
      WorksheetFunction.Transpose(MyRange)
      
End Sub

This particular example will transpose the cells in the range A1:B5 and output the transposed range starting in cell D1.

The following example shows how to use this syntax in practice.

Example: How to Transpose a Range in VBA

Suppose we have the following dataset in Excel that contains information about various basketball players:

Suppose we would like to transpose the range A1:B5 and output the transposed range starting in cell D1.

We can create the following macro to do so:

Sub TransposeRange()

    'specify range to transpose
    MyRange = Range("A1:B5")
    
    'find dimensions of range
    XUpper = UBound(MyRange, 1)
    XLower = LBound(MyRange, 1)
    YUpper = UBound(MyRange, 2)
    YLower = LBound(MyRange, 2)
    
    'transpose range
    Range("D1").Resize(YUpper - YLower + 1, XUpper - XLower + 1).Value = _
      WorksheetFunction.Transpose(MyRange)
      
End Sub

When we run this macro, we receive the following output:

Notice that the transposed range is displayed starting in cell D1.

That is, the rows and the columns are switched.

To transpose a different range, simply change A1:B5 in the macro to a different range.

Note: You can find the complete documentation for the VBA Transpose method here.

Additional Resources

The following tutorials explain how to perform other common tasks in VBA:

VBA: How to Count Cells with Specific Text
VBA: How to Count Number of Rows in Range
VBA: How to Write COUNTIF and COUNTIFS Functions