Transposing list of numbers

  • Thread starter Thread starter jlhcat
  • Start date Start date
J

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.
 
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
 
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.
Click to expand...

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
 
Back
Top