Transpose Data from a column to several rows

[Guest]

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.

 

[Gord Dibben]

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 Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to Tool>Macro>Macros.

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


Gord Dibben MS Excel MVP

 

[Guest]

Hi Gord,

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

Regards,
Stan

 

[Biff]

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

 

[Guest]

Biff,

Thank you too. My problem has been solved.

Regards,

Stan

 

[Biff]

You're welcome!

Biff

Biff,

Thank you too. My problem has been solved.

Regards,

Stan