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.