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

--
Message posted from http://www.ExcelForum.com

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

lvdesouza < wrote:
Dear All,

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

I have data in several thousands of rows (each column corresponds to a
year, I have about a dozen columns).
I want to order this data (copy/transpose) in a single column. I know
how to do that by running the recorder
for one single copy/transpose operation, and I could do that by copying
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 any
one tell me how I could do that?

Best,

Lucio.


---
Message posted from http://www.ExcelForum.com/

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.


Don Lloyd wrote:
*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

"lvdesouza " wrote i
message
...
Dear All,

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

I have data in several thousands of rows (each column correspond
to a
year, I have about a dozen columns).
I want to order this data (copy/transpose) in a single column.
know
how to do that by running the recorder
for one single copy/transpose operation, and I could do that b
copying
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. Ca
any
one tell me how I could do that?

Best,

Lucio.


---
Message posted from http://www.ExcelForum.com/


--
Message posted from http://www.ExcelForum.com

Dear Alan,

Thanks for your reply, I will try it right now.

Best,

Lucio.
Alan Beban wrote:
*Assuming you have 2000 rows of data, and you want to end up wit
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

lvdesouza < wrote:
Dear All,

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

I have data in several thousands of rows (each column correspond
to a
year, I have about a dozen columns).
I want to order this data (copy/transpose) in a single column.
know
how to do that by running the recorder
for one single copy/transpose operation, and I could do that b
copying
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. Ca
any
one tell me how I could do that?

Best,

Lucio.


---
Message posted from http://www.ExcelForum.com/


--
Message posted from http://www.ExcelForum.com

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

"lvdesouza " wrote in message
...
Dear All,

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

I have data in several thousands of rows (each column corresponds to a
year, I have about a dozen columns).
I want to order this data (copy/transpose) in a single column. I know
how to do that by running the recorder
for one single copy/transpose operation, and I could do that by copying
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 any
one tell me how I could do that?

Best,

Lucio.


---
Message posted from http://www.ExcelForum.com/