Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Speed up your file

FastExcel
The best tool to optimise your Excel model!
Home > English site > Articles > Transpose Table
Deze pagina in het Nederlands

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

All comments about this page:


Comment by: Tom (9/19/2006 11:42:12 AM)

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

 


Comment by: Jan Karel Pieterse (9/19/2006 9:14:34 PM)

Hi Tom,

Thanks!

Regards,
Jan Karel Pieterse

 


Comment by: BK. Wibowo (9/19/2006 9:36:04 PM)

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 (9/20/2006 6:25:19 AM)

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 (9/21/2006 10:33:36 AM)

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 (9/28/2006 5:45:32 AM)

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 (9/28/2006 6:26:41 AM)

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 (1/18/2007 1:04:41 AM)

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

 


Comment by: Derek Turner (1/27/2007 7:55:12 AM)

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 (4/7/2007 10:22:29 AM)

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 (5/31/2007 5:11:48 PM)

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 (5/31/2007 9:25:21 PM)

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 (7/19/2007 3:45:15 AM)

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

 


Comment by: rodstar (1/9/2008 6:30:32 AM)

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 (1/9/2008 10:41:14 AM)

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 (4/18/2008 12:35:54 AM)

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 (4/21/2008 7:45:15 AM)

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

 


Comment by: Robert (6/8/2008 5:20:56 AM)

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 (6/8/2008 9:49:27 PM)

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

 


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

 


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]

 


Comment by: Tina (12/6/2012 1:14:48 AM)

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

 


Comment by: dan (2/9/2013 4:36:39 PM)

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

thank you for saving hours of work

 


Comment by: michael fozouni (3/6/2013 4:31:58 AM)

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 (3/6/2013 11:35:25 AM)

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 (3/6/2013 1:01:04 PM)

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 (3/6/2013 7:34:25 PM)

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

 


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

Got it, thanks.
Michael

 


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

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 (9/27/2013 11:17:58 AM)

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 (9/27/2013 8:35:01 PM)

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.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].