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:

An area of cells I wish to transpose

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

The result after pasting I want to achieve

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

The actual result of a paste special transpose

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:

The locals window showing variable contents

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:

The locals window showing variable contents

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

All comments about this page:

Comment by: Tom (19-9-2006 11:42:12) deeplink to this comment

10
excellent example of how to code
as wells as the function preformed.
Tom

Comment by: Jan Karel Pieterse (19-9-2006 21:14:34) deeplink to this comment

Hi Tom,

Thanks!

Regards,
Jan Karel Pieterse

Comment by: BK. Wibowo (19-9-2006 21:36:04) deeplink to this comment

It also can be done by:
Select Range,
Ctrl+H
Find: =
Replace with: blablabla=
Transpose
Select Range again,
Ctrl+H
Find: blablabla=
Replace with: =
The formula will remain the same

Comment by: Jan Karel Pieterse (20-9-2006 06:25:19) deeplink to this comment

Hi BK,

Thanks for your addition.

I did know that one. I deliberately show the VBA method here, because it also shows how to push a range of cells into a VBA variant variable and vice-versa.

Comment by: Raja (21-9-2006 10:33:36) deeplink to this comment

Excelent example Jan. I think it will help a lot when you create reports (what I always do) and your boss needs reports in different style (for some reason).

Comment by: Chris (28-9-2006 05:45:32) deeplink to this comment

Very nice. My only critique is that you use an Exit Sub (a Goto by any other name is still a Goto).

Helpful indeed. I will have to keep this one filed away for future use.

Comment by: Jan Karel Pieterse (28-9-2006 06:26:41) deeplink to this comment

Hi Chris,

Good point. I do use this on occasion, because I prefer it to an If-Then-Else construct that spans almost the entire subroutine. I find those to more confusing than a sparsely used Exit sub when a certain "vital" test fails.

But that is a matter of personal preference I guess.

Comment by: Ufuk (18-1-2007 01:04:41) deeplink to this comment

That's perfect, this macro help me a lot to complate a work, thank you very much!

Comment by: Derek Turner (27-1-2007 07:55:12) deeplink to this comment

Sub TransposeSelection()
'
    TransposeFormulae Selection
End Sub
Sub TransposeFormulae(oSelection As Object)
'
    If TypeName(oSelection) = "Range" Then
        With oSelection
            .Offset(.Rows.Count + 2).Resize(.Columns.Count, .Rows.Count).Formula = Application.Transpose(.Formula)
        End With
    Else
        MsgBox "Please select a range of cells first.", vbOKOnly + vbInformation, "Transpose formulae"
    End If
End Sub

However, this fails in Excel 2002 with a Type Mismatch if any cell contains a string longer than 255 characters. It will also fail with multiple selections but this can be trapped with .Areas.Count

Comment by: Cale Maddy (7-4-2007 10:22:29) deeplink to this comment

Thank you so much for the select range, Ctrl-H method. I don't have a clue about visual basic and you just saved me an enormous amount of time.

Comment by: lennox de ronde (31-5-2007 17:11:48) deeplink to this comment

it can be don much more simple

when you make the formula use $+cell reference and then perform the transpose depending on the tranposeyou use cell$ of $cell
lennox, thanks anyway

Comment by: Jan Karel Pieterse (31-5-2007 21:25:21) deeplink to this comment

Hi Lennox,

You're correct if you did set up those absolute references properly. But how often have people NOT done that...

And my example always works, even with mixed references.

Comment by: John Salkeld (19-7-2007 03:45:15) deeplink to this comment

Very interesting - It would be of interest to include array formulae in this application

Comment by: rodstar (9-1-2008 06:30:32) deeplink to this comment

great solution!
any tutorial teaching how to program this code into Excel? I know a little about coding, but never did anything using VBE.

Other option is offer a xls to download and use the macro.

thanks.

r

Comment by: Jan Karel Pieterse (9-1-2008 10:41:14) deeplink to this comment

Hi rodstar,

Just open the VBE (alt+F11), insert a module (Insert, Module) and paste in the code shown above.

Then return to Excel. Select the range you want processed, hit alt+F8 to get to the macro list and run the macro called TransposeFormulas

Comment by: Danièle Questiaux (18-4-2008 00:35:54) deeplink to this comment

As you say very well, your example first is useful(!), but is also one of those examples that clarifies the steps used in VBA, and that is priceless!
Thanks!
All the added comments are also a great idea to have for novices at VBA (I/m one of them).

Comment by: colin (21-4-2008 07:45:15) deeplink to this comment

Thanks Lennox, the original answer was a little intimidating for the layman

Comment by: Robert (8-6-2008 05:20:56) deeplink to this comment

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 (8-6-2008 21:49:27) deeplink to this comment

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 (1-7-2008 05:58:41) deeplink to this comment

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

Comment by: Jan Karel Pieterse (1-7-2008 10:22:35) deeplink to this comment

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

Comment by: Giovanni (29-12-2008 13:11:50) deeplink to this comment

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

Comment by: Dennis Sawyer (12-2-2009 04:01:53) deeplink to this comment

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 (15-4-2009 01:41:24) deeplink to this comment

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 (15-4-2009 02:43:24) deeplink to this comment

Hi Danièle,

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

Comment by: vino (13-3-2010 02:15:39) deeplink to this comment

Hi.

how to hide the formulas in excel

Comment by: Jan Karel Pieterse (13-3-2010 10:53:49) deeplink to this comment

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 (4-5-2010 20:32:28) deeplink to this comment

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 07:38:25) deeplink to this comment

Hi Dang,

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

Comment by: Nath (18-5-2010 19:22:55) deeplink to this comment

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 (18-5-2010 20:53:10) deeplink to this comment

Hi Nath,

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

Comment by: Nath (18-5-2010 22:36:19) deeplink to this comment

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 (18-5-2010 23:56:51) deeplink to this comment

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 (19-5-2010 04:56:59) deeplink to this comment

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 (28-10-2010 10:34:06) deeplink to this comment

Very well done! Thank you. This helped a lot

Comment by: cOOL_t (12-11-2010 02:17:05) deeplink to this comment

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 (13-11-2010 11:56:56) deeplink to this comment

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 (13-11-2010 12:42:51) deeplink to this comment

to Jan Karel Pieterse

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

Comment by: Sahitya (23-5-2011 05:12:44) deeplink to this comment

[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]

Comment by: Tina (6-12-2012 01:14:48) deeplink to this comment

Amazing!! This did EXACTLY what I needed it to, so glad I found this post! Thank you *so* much =)

Comment by: dan (9-2-2013 16:36:39) deeplink to this comment

this is the best excel macro i have ever seen!!!

thank you for saving hours of work

Comment by: michael fozouni (6-3-2013 04:31:58) deeplink to this comment

Hi,
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 (6-3-2013 11:35:25) deeplink to this comment

Hi Michael,

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 (6-3-2013 13:01:04) deeplink to this comment

Dear Jan,
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 (6-3-2013 19:34:25) deeplink to this comment

The message box should only pop up when you have something else selected, like a chart.

Comment by: Michael (6-3-2013 20:12:31) deeplink to this comment

Got it, thanks.
Michael

Comment by: luciana (26-9-2013 20:19:11) deeplink to this comment

Perfect VBA!! Helped a lot!! Only one question... Is there a way to keep the formating from the orginal table? because when I use the simple ´transpose´ from excel it keeps my formating, like writing in different color, or a cell painted in another color, etc, but it messes up the formulas. but if I use this Macro it keeps the formulas perfectly, but I would have to format all over again.... is there a way to insert the formating in the VBA? I think there isn´t, just thought I should confirm first.
Thank you very much for the Macro though... helps a lot!

Comment by: Jan Karel Pieterse (27-9-2013 11:17:58) deeplink to this comment

Hi Luciana,

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:

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.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 (27-9-2013 20:35:01) deeplink to this comment

It worked perfectly! Thank you!

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.