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

On Wed, 6 Feb 2008 09:47:06 -0800, jlhcat
wrote:

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.