Copying every nth Row

[RobShaw]

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

 

[Tazguy37]

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.

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

 

[RobShaw]

Thanks, worked a treat.