code to cut row and paste it to column

dennisli

Well-known Member
Joined
Feb 20, 2004
Messages
1,070
Good afternoon,
I have a data distributed as shown by table 1 from cell A1: all the data in three rows, each two columns are one calendar year information ( suppose the data spread until the last column). How can cut and paste it into two columns as shown by table 2 from cell A1? Code maybe the only solutions. Right now I just give up my manual cut and paste as I have the data until the last column.
Thanks lot.
Dennis

Table 1
2003 1 2002 3 2001 5
2004 2 2003 4 2002 6
2005 3 2004 5 2003 7
table 2
2003 1
2004 2
2005 3

2002 3
2003 4
2004 5

2001 5
2002 6
2003 7
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
dennisli,
try this
without any checks etcetera ...
Code:
Sub test()
For I = 1 To Columns.Count - 1 Step 2
Range(Cells(1, I), Cells(3, I + 1)).Copy Cells(I * 2 + 3, 1)
Next I
End Sub

kind regards,
Erik
 

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
Hi, Eric. Since I don't do VBA, I came up with a formula solution.

Here is a formula solution, assuming you want the results starting in A6 (don't change A6, or the formulas will have to be adjusted!):

In A6, enter =INDIRECT(CHAR(64+COLUMN()+(ROW()-6)/2)&1)
In A7, enter =INDIRECT(CHAR(64+COLUMN()+(ROW()-7)/2)&1)
In A8, enter =INDIRECT(CHAR(64+COLUMN()+(ROW()-8)/2)&1)
Leave A9 blank.

Highlight the range A6:A8, do a Copy, highlight the range A6:A9+4*n, that is, highlight the range A6:A9 on down in steps of 4, then do a Paste.

Done!
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi, Ralph,
Since I don't do VBA
Do you have a reason for this ?
Knowing both formulas and VBA, you can choose which solution would be the most adequate in the given circumstances.
I love VBA but often choose formula-solution

best regards,
Erik
 

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829

ADVERTISEMENT

Eric, I have used BASIC to program some electrical engineering problems. Also, QuickBASIC 4.5. Just wet my feet in Visal Basic. Looked at VBA, and decided I didn't have enough reason to learn so many things! Everywhere I looked, there were new words, methods, what-have-you to learn, so, i decided to stay clear of VBA, even VB, unless I really can't solve my problems by programming in QuickBASIC. I am retired, 80 years old, and don't have much need to solve new problems, so, I'm taking the easy way, and sticking with what I already know, pretty much.
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
RalphA,
Thank you for responding!
Good to know why people make their decisions.
With all my respect :)
Erik
 

Watch MrExcel Video

Forum statistics

Threads
1,118,111
Messages
5,570,254
Members
412,313
Latest member
pauloalex
Top