I have a spreadsheet in Excel 2003. I have data in cells A1 thru
J3950. I want to move the data in every other row (the even rows) to
the odd rows, after the existing data in the odd rows. The data in
the odd rows ends in column J. So I want to move the data from
columns A to H in the even rows to columns K to R in the odd rows.
My worksheet is Sheet2a

This is my code:

Sub ShiftEvenRows()

With Worksheets("Sheet2a")
For i = 2 To 6950 Step 2
Set r2 = .Range("A(i):J(i)")
Set r1 = .Range("K(i-1):R(i-1)")
r1.Cut.r2

Next

End With
End Sub

I may have multiple errors. I get the error message "Subscript out of
Range."

Any help would be appreciated.

If anyone is willing to contact me off-line, my email addresses a



and



Thank you in advance.

I think this will do what you want...

Sub EvenToOddRows()
Dim X As Long
Dim LastRow As Long
Dim Evens As Range
With Worksheets("Sheet1")
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
For X = 2 To LastRow Step 2
.Range("A" & X & ":J" & X).Copy .Cells(X - 1, "K")
.Range("A" & X & ":J" & X).Clear
Next
End With
End Sub

Note: I made the assumption that the number of rows of data could vary and
set the code up to work down to the last row of data.

Rick


"Steve G" wrote in message
...
I have a spreadsheet in Excel 2003. I have data in cells A1 thru
J3950. I want to move the data in every other row (the even rows) to
the odd rows, after the existing data in the odd rows. The data in
the odd rows ends in column J. So I want to move the data from
columns A to H in the even rows to columns K to R in the odd rows.
My worksheet is Sheet2a

This is my code:

Sub ShiftEvenRows()

With Worksheets("Sheet2a")
For i = 2 To 6950 Step 2
Set r2 = .Range("A(i):J(i)")
Set r1 = .Range("K(i-1):R(i-1)")
r1.Cut.r2

Next

End With
End Sub

I may have multiple errors. I get the error message "Subscript out of
Range."

Any help would be appreciated.

If anyone is willing to contact me off-line, my email addresses a



and



Thank you in advance.

And yet another way -

Sub test()

With Worksheets("Sheet1")
.Range("A2:H3950").Copy .Range("K1:R3949")
For i = 2 To 3950 Step 2
.Range(.Cells(i, 1), .Cells(i, 18)).Clear
Next
End With

End Sub


I notice in your code you loop to 6950, adapt the above to suit (not least
the sheet name).


Regards,
Peter T



"Steve G" wrote in message
...
I have a spreadsheet in Excel 2003. I have data in cells A1 thru
J3950. I want to move the data in every other row (the even rows) to
the odd rows, after the existing data in the odd rows. The data in
the odd rows ends in column J. So I want to move the data from
columns A to H in the even rows to columns K to R in the odd rows.
My worksheet is Sheet2a

This is my code:

Sub ShiftEvenRows()

With Worksheets("Sheet2a")
For i = 2 To 6950 Step 2
Set r2 = .Range("A(i):J(i)")
Set r1 = .Range("K(i-1):R(i-1)")
r1.Cut.r2

Next

End With
End Sub

I may have multiple errors. I get the error message "Subscript out of
Range."

Any help would be appreciated.

If anyone is willing to contact me off-line, my email addresses a



and



Thank you in advance.

On Jun 30, 4:13*pm, "Peter T" <peter_t@discussions wrote:
And yet another way -

Sub test()

With Worksheets("Sheet1")
* * .Range("A2:H3950").Copy .Range("K1:R3949")
* * For i = 2 To 3950 Step 2
* * * * .Range(.Cells(i, 1), .Cells(i, 18)).Clear
* * Next
End With

End Sub

I notice in your code you loop to 6950, adapt the above to suit (not least
the sheet name).

Regards,
Peter T

"SteveG" wrote in message

...



I have a spreadsheet in Excel 2003. *I have data in cells A1 thru
J3950. *I want to move the data in every other row (the even rows) to
the odd rows, after the existing data in the odd rows. *The data in
the odd rows ends in column J. *So I want to move the data from
columns A to H in the even rows to columns K to R in the odd rows.
*My worksheet is Sheet2a

This is my code:

Sub ShiftEvenRows()

With Worksheets("Sheet2a")
For i = 2 To 6950 Step 2
Set r2 = .Range("A(i):J(i)")
Set r1 = .Range("K(i-1):R(i-1)")
r1.Cut.r2

Next

End With
End Sub

I may have multiple errors. *I get the error message "Subscript out of
Range."

Any help would be appreciated.

If anyone is willing to contact me off-line, my email addresses a



and



Thank you in advance.- Hide quoted text -

- Show quoted text -

To : Rick Rothstein and Peter T--

I will try your code tomorrow. Thank you both very much.

Sincerely,

Steve Greenspan

On Jun 30, 1:14*pm, "Rick Rothstein \(MVP - VB\)"
wrote:
I think this will do what you want...

Sub EvenToOddRows()
* Dim X As Long
* Dim LastRow As Long
* Dim Evens As Range
* With Worksheets("Sheet1")
* * LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
* * For X = 2 To LastRow Step 2
* * * .Range("A" & X & ":J" & X).Copy .Cells(X - 1, "K")
* * * .Range("A" & X & ":J" & X).Clear
* * Next
* End With
End Sub

Note: I made the assumption that the number of rows of data could vary and
set the code up to work down to the last row of data.

Rick

"Steve G" wrote in message

...



I have a spreadsheet in Excel 2003. *I have data in cells A1 thru
J3950. *I want to move the data in every other row (the even rows) to
the odd rows, after the existing data in the odd rows. *The data in
the odd rows ends in column J. *So I want to move the data from
columns A to H in the even rows to columns K to R in the odd rows.
My worksheet is Sheet2a

This is my code:

Sub ShiftEvenRows()

With Worksheets("Sheet2a")
For i = 2 To 6950 Step 2
Set r2 = .Range("A(i):J(i)")
Set r1 = .Range("K(i-1):R(i-1)")
r1.Cut.r2

Next

End With
End Sub

I may have multiple errors. *I get the error message "Subscript out of
Range."

Any help would be appreciated.

If anyone is willing to contact me off-line, my email addresses a



and



Thank you in advance.- Hide quoted text -

- Show quoted text -

Mr. Rothstein--

I just wanted to thank you. I ran the macro you wrote for me last
wek. It worked flawlessly. If you ever need a favor in the Washinton
DC area, please let me know. Thank you again. Steve Greenspan