Table of Contents
In VBA, transposing a range means to switch the rows and columns of a range of cells. To transpose a range, you can use the Transpose method which is part of the Range object. It takes a range and transposes it in-place, meaning it replaces the original range with the transposed one. You can also use the PasteSpecial method to transpose a range, by copying and pasting it while specifying the Transpose option.
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 .