Transpose Rows, Columns and Tables

By Jeff Robson

Transposing a Single Row or Column

If you only have a single row or column of data, you can transpose this into a column or row by using the TRANSPOSE array function.

For example, if your data is in cells Sheet1!A1:A5 and you want to put this into Sheet2!A1:E1, you simple select the range Sheet2!A1:E1 then enter =TRANSPOSE(Sheet1!A1:A5) and press Ctrl-Shift-Enter

While this method is fine for a single row or column, applying it to an entire table is a bit painful.

Transposing a 2D Table in Memory using VBA

If you just want to transpose a table of values, the easiest way is via Copy > Paste Special > Values > Transpose

If you want to do this to a 2D table of values via VBA, just use the VBA function below:

‘Purpose   :    Transposes a 2D array
‘Inputs    :    avValues                    The array to transpose.
‘Outputs   :    Returns the array transposed, or empty if an error occurs
‘Author    :    Andrew Baker
‘Date      :    25/03/2000
‘Notes    

Function Array2DTranspose(avValues As Variant) As Variant
Dim lThisCol As Long, lThisRow As Long
Dim lUb2 As Long, lLb2 As Long
Dim lUb1 As Long, lLb1 As Long
Dim avTransposed As Variant

Transposing the Current 2D Region

By slightly modifying this code, you could also create a keyboard shortcut that will transpose the current region of data (see code below).

To use this, select any cell in the table you want to transpose and run the macro.  It inserts a new sheet with the transposed table.

To assign a keyboard shortcut, use Alt-F8 > Options

Sub TransposeArray()
    ‘macro to transpose a 2D array
    ‘selects the current area then transposes this & pastes the result onto a new sheet
    ‘by Jeff Robson
    ‘www.accessanalytic.com.au
    ‘Oct 2013
    ‘incorporates code from http://vbusers.com/code/codeget.asp?ThreadID=555&PostID=1

    Dim lThisCol As Long, lThisRow As Long
    Dim lUb2 As Long, lLb2 As Long
    Dim lUb1 As Long, lLb1 As Long
    Dim avTransposed As Variant
    Dim wksNew As Worksheet
    Dim avValues() As Variant

    avValues() = Selection.CurrentRegion.Value

    If IsArray(avValues) Then
        On Error GoTo ErrFailed
        lUb2 = UBound(avValues, 2)
        lLb2 = LBound(avValues, 2)
        lUb1 = UBound(avValues, 1)
        lLb1 = LBound(avValues, 1)

        ReDim avTransposed(lLb2 To lUb2, lLb1 To lUb1)
        For lThisCol = lLb1 To lUb1
            For lThisRow = lLb2 To lUb2
                avTransposed(lThisRow, lThisCol) = avValues(lThisCol, lThisRow)
            Next
        Next
    End If

ErrFailed:
    Debug.Print Err.Description
    Exit Sub

End Sub