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