This macro will transpose the 1422 cells to 5.6 rows but unless you wait for
Excel 2007 which has 16,384 columns, you cannot squeeze the 1422 into one row.

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 = 255
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 Fri, 22 Sep 2006 08:43:18 -0600, "Gary" wrote:

Is it possible? since we dont have 1422 columns, how would the data be
transposed?

Thanks in advance.