Excel Macro - How to Select Next Row

[Dean]

I'm trying to record a macro which will advance the row
selection by one row, then insert a row. When I record
the macro and look at the VB code that it generates, it
makes reference to the specific row via a "Range"
statement - e.g. Range("10:10") for row 10. I want to
make a relative reference to the row, so that regardless
of what row is selected, the macro will advance to the
next row. (I remeber way back when ... when a macro
would make reference to moving to a new cell by virtue of
something like R+1, C+2, to designate that the cursor
would move to the cell that was one row down and two
columns to the right ... that is the type of thing that I
would like to do, but need to select a row, then run a
macro which Inserts a new row, then advances the
RowSelected to the row which is 2 rows down .
Any idea how to do that? Email me ... would be greatly
appreciated.
Thanks.
Dean

 

[J.E. McGimpsey]

One way:

Public Sub InsertRow()
With ActiveCell
.Offset(1, 0).EntireRow.Insert
.Offset(2, 0).Select
End With
End Sub

If you're not familiar with the With...End With structure, the dot
(.) stands for whatever is after "With" so the above is a bit
faster/more efficient than writing

Public Sub InsertRow()
ActiveCell.Offset(1, 0).EntireRow.Insert
ActiveCell.Offset(2, 0).Select
End Sub