PDA

View Full Version : [SOLVED] Transpose



RobertBC
03-03-2005, 07:34 AM
How can I transpose row data regardless the # of rows to transpose:friends:

Aaron Blood
03-03-2005, 09:22 AM
It'd be kinda hard to transpose more than 256 rows dontcha think?

What do you want to do here? A macro to create a transpose function for a selected range? Or... just transpose the values... or what?

RobertBC
03-03-2005, 09:42 PM
Hey men! thanks for your concern.

what i am looking for is how can i create macro/procedure to transpose row data regardless the # of rows and columns.
:bow: :bow: :bow: :help

Jacob Hilderbrand
03-03-2005, 10:13 PM
This macro will transpose the data from Column A regardless of the number of Rows to Row 1 (starting at B1).

But like Arron states, if you have too many rows, you will error out.



Option Explicit

Sub Macro1()
Dim LastRow As Long
LastRow = Range("A65536").End(xlUp).Row
Range("A1:A" & LastRow).Copy
Range("B1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
End Sub

RobertBC
03-03-2005, 11:16 PM
Tnx DRJ i'll Try it out :friends:

TonyJollans
03-04-2005, 10:25 AM
Hi Robert,

You don't seem to want to let on very much about what you're doing. You want to transpose any number of rows is about all I can fathom.

Well, as has already been stated, as there are only 256 columns you cannot transpose more rows than 256 into columns. One can, however, transpose more than 256 rows into an array.

The worksheet transpose function has limits - surprisingly, perhaps, greater than 256 - the actual number of rows varying depending on the number of columns (and maybe other factors).

If you really want an unlimited transpose function for a two-dimensional array you will have to write code to do it yourself. If you are actually trying to do something specific then why not tell us about it? It will make it easier for us to help

patrickab
03-11-2005, 01:21 PM
The TRANSPOSE function is a bit odd in how it works (or at least I think it's a bit odd) anyhow it works as follows:

1. In the first cell you want the data transferred TO enter the formula =TRANSPOSE(range_of_source_data).
2. Drag/copy this formula over the new range of cells that you want the data transferred to. The cells should all have #VALUE in them.
3. Whilst the cells are highlighted in the range of cells you want the data moved TO, press F2 and then hold down Control and Shift at the same time and then press Enter - then release all the keys. This will convert the formulae into array formulae and copy all the items that were in the column into the cells in the row. You will know if you have been successful as the data should appear and all the formulae should have curly brackets around them - for example {=TRANSPOSE(A2:A5)}

TRANSPOSE will transfer data in a column to rows or vice versa.

Clearly as a worksheet can only have 256 columns you can only transpose 256 rows to columns or vice versa.

Patrick

Aaron Blood
03-11-2005, 02:21 PM
The TRANSPOSE function is a bit odd in how it works (or at least I think it's a bit odd) anyhow it works as follows:

1. In the first cell you want the data transferred TO enter the formula =TRANSPOSE(range_of_source_data).
2. Drag/copy this formula over the new range of cells that you want the data transferred to. The cells should all have #VALUE in them.
3. Whilst the cells are highlighted in the range of cells you want the data moved TO, press F2 and then hold down Control and Shift at the same time and then press Enter - then release all the keys. This will convert the formulae into array formulae and copy all the items that were in the column into the cells in the row. You will know if you have been successful as the data should appear and all the formulae should have curly brackets around them - for example {=TRANSPOSE(A2:A5)}

Patrick

Yes, the way you do it is indeed odd (or at least I think it's a bit odd).

The function itself is as odd as any other array formula in Excel.

Zack Barresse
03-11-2005, 02:39 PM
The other large problem with having large Transposed array's, imho, is the fact that you cannot edit only part of an array, you must edit the entire array or none of it.

Be sure and read the help files on Array Functions. A board search will yield some good results as well.

patrickab
03-11-2005, 03:47 PM
Aaron - Please educate me in the 'better' way of using =TRANSPOSE() - I genuinely would appreciate your instructions. Patrick

Zack Barresse
03-11-2005, 04:22 PM
Take out step 1 and 2 completely. During step 3, this is where you enter the formula.

patrickab
03-11-2005, 04:30 PM
Zack - In my tests it doesn't make it any simpler at all! Oh well, each to their own. - Patrick

Zack Barresse
03-11-2005, 04:52 PM
.. then my personal favorite ..

1) Select Data --> Copy
2) Alt + E, S, E (, V - for values)

.. that's it. I think it's much simpler.

patrickab
03-12-2005, 05:01 AM
Zack - Agreed - that's a simpler route - Patrick

Aaron Blood
03-14-2005, 06:45 AM
Aaron - Please educate me in the 'better' way of using =TRANSPOSE() - I genuinely would appreciate your instructions. Patrick

Select the range to hold the array formula.
Input the formula.
Instead of pressing enter, press Ctrl-Shift-Enter.