Moving data in Excel 2003 via VBA
 

I have a spreadsheet in Excel 2003. I has 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.

Steve G

Moving data in Excel 2003 via VBA
 

Hi Steve

Try this

Sub ShiftEvenRows()
Application.ScreenUpdating=False
Worksheets("Sheet2a").Activate
For i = 2 To 6950 Step 2
Set r1 = Range(Cells(i, "A"), Cells(i, "J"))
Set r2 = Cells(i - 1, "K")
r1.Cut r2
Next
Application.ScreenUpdating=True
End Sub

Regards,
Per

"Steve G" skrev i meddelelsen
...
I have a spreadsheet in Excel 2003. I has 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.

Steve G