Copying every nth Row

RobShaw

Board Regular
Joined
Dec 21, 2004
Messages
76
In really old versions of excel you select absolute or referential cell noting when recording macros. I have excel 2000 and I set the R1C1 style in the options and the macros still record with the traditional A1 style.

I have a list of information pasted froma website in the following form
Name
Address
Tel number
Hyperlink
name
address
...
... etc.

I need to move every nth row accross and Iused to do this by recording a macro and running the amcro lots of times via a short cut key.

Is there a better way of doing this first of all, or failing that is there a way to record macros with the R1C1 style.

Thanks in advance
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
I don't think you can get the recorder to use R1C1, but you can change the sheet to R1C1 and then view the formulas (Control-~) to see what the R1C1 formulas would be. Anyway, this code should do what you're after. Please post back with any questions or problems.

Code:
Sub movetest()
'Assumes data starts at A1, no column or row labels and no blank rows
Dim i, lastRow As Long
i = 1
    
Do
    Range("A" & i).Offset(1, 0).Resize(3).Copy
    Range("B" & i).PasteSpecial xlPasteAll, xlPasteSpecialOperationNone, , True
    Range("A" & i).Offset(1, 0).Resize(3).Delete shift:=xlUp
    i = i + 1
Loop Until Range("A" & i).Value = ""

End Sub
 

Forum statistics

Threads
1,147,621
Messages
5,742,193
Members
423,710
Latest member
Duarte85

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top