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

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.

Comment by: Dang (5/4/2010 8:32:28 PM)

I have excel with the following format

A A1 A2 A3 A4
B B1 B2 B3 B4

and would like to change into the following format

A A1
A A2
A A3
A A4
B B1
B B2
B B3
B B4

How do I do this in excle please advice...

Thank you,
Dang

Comment by: Jan Karel Pieterse (5/5/2010 7:38:25 AM)

Hi Dang,

I recommend you to ask questions like this one at www.eileenslounge.com

Comment by: Nath (5/18/2010 7:22:55 PM)

How to make a cell resize to a picture or object such as word when pasted on to a cell, so that you dont need to hover and resize the cell manually; i am not a VBA user; is ther any trick not using VBA , please

Comment by: Jan Karel Pieterse (5/18/2010 8:53:10 PM)

Hi Nath,

I wouldn't know how, except by use of VBA.
Sorry to be of little help there.

Comment by: Nath (5/18/2010 10:36:19 PM)

Can i venture into VBA for the same task of resizing an object in to a specifc cel size without handling the edges; infact i was very much helped by your topic in 'Show picture'. I saw a couple of them on you tube videos. But yours was the best, so easy to undestand even without a video clip; thanks mate; help me if you can to resize an object to a cell the moment i paste it using vba! many thanks

Comment by: Jan Karel Pieterse (5/18/2010 11:56:51 PM)

Hi Nath,

If you select the picture object, then this macro will size it according to cell dimensions:

Sub SizePic()
    With Selection
        .Width = .TopLeftCell.Width
        .Height = .TopLeftCell.Height
    End With
End Sub

Note that this may change the aspect ratio of the picture.

Comment by: Nath (5/19/2010 4:56:59 AM)

Thanks Jan, without a clue, i copy pasted and tried using on a picture and what magic, it just solved it! thanks a lot....

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

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.