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 27 in total (Show All Comments):

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.

Comment by: vino (3/13/2010 2:15:39 AM)

Hi.

how to hide the formulas in excel

Comment by: Jan Karel Pieterse (3/13/2010 10:53:49 AM)

Hi Vino,

To hide your formulas, you need to:
1. Select the cells
2. Hit Control+1 to get to the cell properties dialog
3. Click the Protection tab
4. Check "Hidden"
5. Protect the worksheet

Remember to unlock cells the user needs to have access to.

Transposing the Formulas in a Table of Cells

Comments

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