Transposing list of numbers

[jlhcat]

I'm using Excel 2003. I have a list of 400 numbers in Column A, I would like
to transpose it across 7 columns and 58 rows. Is there an easy way to do
this? I understand I can do the copy/paste special/transpose for 7 at a
time. Thank you.

 

[Gord Dibben]

Sub ColtoRows()
Dim rng As Range
Dim i As Long
Dim J As Long

Set rng = Cells(Rows.Count, 1).End(xlUp)
J = 1
On Error Resume Next
nocols = 7 '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


Gord Dibben MS Excel MVP

 

[Ron Rosenfeld]

I'm using Excel 2003. I have a list of 400 numbers in Column A, I would like
to transpose it across 7 columns and 58 rows. Is there an easy way to do
this? I understand I can do the copy/paste special/transpose for 7 at a
time. Thank you.

B1: =INDEX($A:$A,COLUMNS($A:A)+(ROWS($1:1)-1)*7,1)

Fill right to Column H, then select B1:H1 and fill down to row 60 or so.

If your data does not start in A1 -- for example, if it starts in A3, then
merely change the array argument as appropriate:

=INDEX($A$3:$A$500,COLUMNS($A:A)+(ROWS($1:1)-1)*7,1)

The other arguments remain unchanged.
--ron