need to transpose 1422 values from a column to row

[Gary]

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

Thanks in advance.

 

[Guest]

You would have to transpose it in 1422/255 rows.

Or, download XL 2007 beta which has 16000 rows.

Dave

 

[Gord Dibben]

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