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