Relative cell references does not work

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
Back
Top