Transpose Data from a column to several rows
 

I would like to take the first 13 cells in the column and display in one row
and then the next 13 cells in the next row and so on...I have seen many posts
that come close but I was not able to get them to suit my needs. Thanks in
advance for your help.

Transpose Data from a column to several rows
 

stan

Sub ColtoRows()
Dim Rng As Range
Dim I As Long
Dim j As Long
Dim nocols As Long
Set Rng = Cells(Rows.Count, 1).End(xlUp)
j = 1
On Error Resume Next
nocols = InputBox("Enter Number of Columns Desired")

For I = 1 To Rng.Row Step nocols
Cells(j, "A").Resize(1, nocols).Value = _
Application.Transpose(Cells(I, "A").Resize(nocols, 1))
j = j + 1
Next
Range(Cells(j, "A"), Cells(Rng.Row, "A")).ClearContents

End Sub

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to ToolMacroMacros.

You can also assign this macro to a button or a shortcut key combo.


Gord Dibben MS Excel MVP

On Sun, 30 Jul 2006 12:47:01 -0700, stansdl
wrote:

I would like to take the first 13 cells in the column and display in one row
and then the next 13 cells in the next row and so on...I have seen many posts
that come close but I was not able to get them to suit my needs. Thanks in
advance for your help.

Transpose Data from a column to several rows
 

Hi Gord,

Thanks for the reply. I will try this although I am not very familiar with
Macros and VBA.

Regards,
Stan

"Gord Dibben" wrote:

stan

Sub ColtoRows()
Dim Rng As Range
Dim I As Long
Dim j As Long
Dim nocols As Long
Set Rng = Cells(Rows.Count, 1).End(xlUp)
j = 1
On Error Resume Next
nocols = InputBox("Enter Number of Columns Desired")

For I = 1 To Rng.Row Step nocols
Cells(j, "A").Resize(1, nocols).Value = _
Application.Transpose(Cells(I, "A").Resize(nocols, 1))
j = j + 1
Next
Range(Cells(j, "A"), Cells(Rng.Row, "A")).ClearContents

End Sub

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to ToolMacroMacros.

You can also assign this macro to a button or a shortcut key combo.


Gord Dibben MS Excel MVP

On Sun, 30 Jul 2006 12:47:01 -0700, stansdl
wrote:

I would like to take the first 13 cells in the column and display in one row
and then the next 13 cells in the next row and so on...I have seen many posts
that come close but I was not able to get them to suit my needs. Thanks in
advance for your help.

Transpose Data from a column to several rows
 

Hi!

Try this:

=INDEX($A:$A,(ROWS($1:1)-1)*13+COLUMNS($A:B)-1)

Copy across to 13 cells then down as needed.

Biff

"stansdl" wrote in message
...
I would like to take the first 13 cells in the column and display in one
row
and then the next 13 cells in the next row and so on...I have seen many
posts
that come close but I was not able to get them to suit my needs. Thanks
in
advance for your help.

Transpose Data from a column to several rows
 

Biff,

Thank you too. My problem has been solved.

Regards,

Stan

"Biff" wrote:

Hi!

Try this:

=INDEX($A:$A,(ROWS($1:1)-1)*13+COLUMNS($A:B)-1)

Copy across to 13 cells then down as needed.

Biff

"stansdl" wrote in message
...
I would like to take the first 13 cells in the column and display in one
row
and then the next 13 cells in the next row and so on...I have seen many
posts
that come close but I was not able to get them to suit my needs. Thanks
in
advance for your help.

Transpose Data from a column to several rows
 

You're welcome!

Biff

"stansdl" wrote in message
...
Biff,

Thank you too. My problem has been solved.

Regards,

Stan

"Biff" wrote:

Hi!

Try this:

=INDEX($A:$A,(ROWS($1:1)-1)*13+COLUMNS($A:B)-1)

Copy across to 13 cells then down as needed.

Biff

"stansdl" wrote in message
...
I would like to take the first 13 cells in the column and display in one
row
and then the next 13 cells in the next row and so on...I have seen many
posts
that come close but I was not able to get them to suit my needs.
Thanks
in
advance for your help.