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

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....

Comment by: John R (10/28/2010 10:34:06 AM)

Very well done! Thank you. This helped a lot

Comment by: cOOL_t (11/12/2010 2:17:05 AM)

Hi everybody!
This is almost exactly what I needed. So, I have a question for you:

How is it possible to alter the sub in order to transpose the data from Sheet1 to Sheet2?

P.S. My knowledge of VBA is almost non-existent, so please keep it simple.

TX in advance!

Comment by: Jan Karel Pieterse (11/13/2010 11:56:56 AM)

Hi cOOL_t,

You must change this line :

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

to:

Worksheets("Sheet2").Range("A1").Resize(oSel.Columns.Count, oSel.Rows.Count).Formula = vFormulas

Comment by: cOOL_t (11/13/2010 12:42:51 PM)

to Jan Karel Pieterse

TX, it's working like a charm. Much oblidged!

Comment by: Sahitya (5/23/2011 5:12:44 AM)

[VBA}

Hi to all
Am new to arcobjects can anyone help me about export attribute table to excel with transpose option..

Plz help me

Advance Thanks..

[/VBA]

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.