Transposing the Formulas in a Table of Cells

Recently someone asked me if it was possible to transpose a table of cells, but in such a way that the formula of each cell is kept intact. I decided VBA was the way to go and produced a small but very useful little routine for that.

Imagine the following table:

Now I'd like to transform that table into this one (see highlighted range):

If you try a copy > paste special > formulas > transpose, this is what you get:

But it is not what we want, is it.

With the following piece of VBA, you can easily transpose your table, without garbling your formulas:

Option Explicit

Sub TransposeFormulas()
    Dim vFormulas As Variant
    Dim oSel As Range
    If TypeName(Selection) <> "Range" Then
        MsgBox "Please select a range of cells first.", _
                vbOKOnly + vbInformation, "Transpose formulas"
        Exit Sub
    End If
    Set oSel = Selection
    vFormulas = oSel.Formula
    vFormulas = Application.WorksheetFunction.Transpose(vFormulas)
    oSel.Offset(oSel.Rows.Count + 2).Resize(oSel.Columns.Count, oSel.Rows.Count).Formula = vFormulas
End Sub

So how does this work?

First we check whether a range of cells has been selected, otherwise the code will produce a runtime error:

    If TypeName(Selection) <> "Range" Then
        MsgBox "Please select a range of cells first.", _
                vbOKOnly + vbInformation, "Transpose formulas"
        Exit Sub
    End If

Next, we store the selected cells in an object variable (which is not really necessary), so we can work with that set of cells:

Set oSel = Selection

Now the formulas of these cells are pulled into a variable of type Variant in one go:

vFormulas = oSel.Formula

If you step through the code (using my example) the Locals window of the Visual Basic Editor (VBE) shows this for the variable vFormulas after executing the statement above:

As you can see, vFormulas is a 4*3 array.

Next we want to swap rows and columns of the vFormulas variable, for which I use the Transpose worksheet function:

vFormulas = Application.WorksheetFunction.Transpose(vFormulas)

The structure of vFormulas changes to this:

Presto! We now have a 4 * 3 array.

Finally, we need to push back the formulas to the worksheet. To be able to do that, we must specify a target range of the exact dimensions as the array variable. For that I use the Resize method. Note I also move down a couple of cells using Offset, so the copy of the table is placed 2 rows below the original:

oSel.Offset(oSel.Rows.Count + 2).Resize(oSel.Columns.Count, oSel.Rows.Count).Formula = vFormulas

Done!

Comments

Showing last 8 comments of 25 in total (Show All Comments):

Comment by: Robert (6/8/2008 5:20:56 AM)

This works great. However it does not work with array formulas (the type you have
to press <CRTL><SHIFT><ENTER> to create.

Any idea how to make it work for arrays. I have tables as big as 64*64 and once I
transposed them I have to click on each cell after the subroutine to press
<CRTL><SHIFT><ENTER> and make then array again (the sub routine transfers them back
to regular formulas)

By the way your explanation on top is great and very easy to follow.

Comment by: Jan Karel Pieterse (6/8/2008 9:49:27 PM)

Hi Robert,

Indeed, it fails with array formulas. For single cell array formulas, this
workaround works:

Sub TransposeFormulas()
    Dim vFormulas As Variant
    Dim oSel As Range
    Dim oCell As Range
    If TypeName(Selection) <> "Range" Then
        MsgBox "Please select a range of cells first.", _
                vbOKOnly + vbInformation, "Transpose formulas"
        Exit Sub
    End If
    Set oSel = Selection
    vFormulas = oSel.Formula
    vFormulas = Application.WorksheetFunction.Transpose(vFormulas)
    oSel.Offset(oSel.Rows.Count + 2).Resize(oSel.Columns.Count,
oSel.Rows.Count).Formula = vFormulas
    For Each oCell In oSel.Offset(oSel.Rows.Count + 2).Resize(oSel.Columns.Count,
oSel.Rows.Count).Cells
        oCell.FormulaArray = oCell.Formula
    Next
End Sub

Comment by: peter nthiga (7/1/2008 5:58:41 AM)

pliz help on how to transpose access table rows to column in vb 6

Comment by: Jan Karel Pieterse (7/1/2008 10:22:35 AM)

For help with Access I suggest you try the microsoft Access newsgroups.

Comment by: Giovanni (12/29/2008 1:11:50 PM)

Excellent does what it says on the tin. Did exactly what I wanted, Thanks, saved me a lot of time!

Comment by: Dennis Sawyer (2/12/2009 4:01:53 AM)

You are a flippin life saviour. I have 7 sheets i need to do this on and this code has just saved me about 7 hours work. Thank you thank you works a treat

Comment by: Danièle (4/15/2009 1:41:24 AM)

Real time saviour on all counts!
I copied the code in a module no problem, but getting back to it, I realised that you have not commented at the beginning when it was written, your name as author etc.
Module codes are in general commented with the author's name, and purpose (the name here says it all), and it is great to get that as authors tend to have their own style of coding, and as a learning tool, that information helps recognise the differences between codes. It is all a selfish request really, but it is also nice to pass on code snippets with their authors names!
I remembered where i got this one from no problem, so added youe name in a comment form .
Thanks for really great code (this one and many others, but alo for the precise explanation of how it all works.
Many Thanks!

Comment by: Jan Karel Pieterse (4/15/2009 2:43:24 AM)

Hi Danièle,

Thanks for your comments, and you're right, I should start adding my name to the routines.

Transposing the Formulas in a Table of Cells

Comments

Have a question, comment or suggestion? Then please use this form.