"Transpose" Macro.

L

lvdesouza

Dear All,

I am a new user of VBA macros in Excel, and I want to do a very basi
operation.

I have data in several thousands of rows (each column corresponds to
year, I have about a dozen columns).
I want to order this data (copy/transpose) in a single column. I kno
how to do that by running the recorder
for one single copy/transpose operation, and I could do that by copyin
the commands below 2000 times,

Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Range("T2").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone
SkipBlanks:=False _
, transpose:=True
and so on....

but I assume I can do that with a single "loop" instruction. Can an
one tell me how I could do that?

Best,

Lucio
 
A

Alan Beban

Assuming you have 2000 rows of data, and you want to end up with that
data in T2:T24001 as you indicated

Sub c()
Dim rngIn As Range, rngOut As Range, i As Long
Set rngIn = Range("A2:L2000")
Set rngOut = Range("T2")
For i = 1 To rngIn.Rows.Count
rngIn.Rows(i).Copy
rngOut(1 + (rngIn.Columns.Count) * (i - 1)).PasteSpecial _
Transpose:=True
Next
Application.CutCopyMode = False
End Sub

Alan Beban
 
L

lvdesouza

Dear Don,

Thanks for your reply. As I indicated in my message, I want to put the
in a single column (i.e., on the top of each other). As the last time
checked Excel had roughly 65.000 rows, this kind of seems feasible t
me.

Lucio.
 
D

Don Lloyd

Hi Lucio,

You Paste Transpose from Row 2 to Column T.
Where do propose to put the other thousands of rows ?
You only have 256 columns on a sheet.

Regards,
Don
 
Top