Relative cell references does not work

[Peter]

I would like to build macros including relative cell references, but whenever I try it, even with the R1C1 stuff, it never really does the relative references even though MS Excel Help says it can do it.

As a simple example, let's say I want to build a macro that deletes the next 20 odd-numbered rows from wherever I am in the spreadsheet. I build the macro in row one, and it deletes the rows 1,3,5...37,and 39. I close out the macro, then tab down to row 101 and re-run the macro, and instead of deleting 101,103,105 etc, it goes back up to the top and deletes the "new" rows 1,3,5...37,and 39 again

I once called a radio call in show and their "experts" said MS Excel simply does not do what it says it can do regarding this capability. Any advice appreciated. Thanks.

 

[Frank Kabel]

Hi Peter
try something like the following:
Sub delete_rows()
Dim RowNdx As Long
Dim LastRow As Long
Dim Rowstart

Row_start = activecell.row
if rowstart mod 2 = 0 then
rowstart = rowstart+1
end if
lastrow = rowstart + 40
Application.ScreenUpdating = False
For RowNdx = LastRow To rowstart Step -2
Rows(RowNdx).Delete
Next RowNdx
Application.ScreenUpdating = True
End Sub