# 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 49 in total (Show All Comments):Comment by: michael fozouni (3/6/2013 4:31:58 AM)

First, let me extend my sincere thanks and gratitude for all your work in providing great tips on excel.

Second, I ran your "Transpose" macro without selecting a range first. However, the routine did not prompt to ask me to select a range. Am I doing something wrong here maybe?

Many thanks in advance.

Michael

Comment by: Jan Karel Pieterse (3/6/2013 11:35:25 AM)

The macro takes whatever the current selection is and pastes the transposed formulas of that selection below the selection. There are no prompts.

Comment by: Michael (3/6/2013 1:01:04 PM)

Thanks much for your prompt response. I guess whereI fail to understand is the message box where it asks you to select a range?

In any event, many many thanks for answering my question and once again, congratulations on your invaluable site!

Michael

Comment by: Jan Karel Pieterse (3/6/2013 7:34:25 PM)

Comment by: Michael (3/6/2013 8:12:31 PM)

Michael

Comment by: luciana (9/26/2013 8:19:11 PM)

Thank you very much for the Macro though... helps a lot!

Comment by: Jan Karel Pieterse (9/27/2013 11:17:58 AM)

You could add the paste-special transpose to the macro as a first step, after which you let the rest of the macro do the formula trick:

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

oSel.Offset(oSel.Rows.Count + 2).PasteSpecial Paste:=xlPasteAll, Transpose:=True

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

End Sub

Comment by: luciana (9/27/2013 8:35:01 PM)

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