Excel2K - Relative row insert in macro

[thedaver]

The below macro cuts an absolute Row, navigates via ENDownarrow to the end of a list, and inserts the row on the first blank line after the list.

Sub grandtotal()
Rows("2:2&quot.Select
Application.CutCopyMode = False
Selection.Cut
Range("C3&quot.Select
Selection.End(xlDown).Select
Rows("35:35&quot.Select
Selection.Insert Shift:=xlDown
End Sub

Problem, the absolute row reference for the insertion isn't going to work since my list is of variable length.

How do I alter the Rows("35:35&quot.Select to instead select the current row (the selection after the Navigation is on the correct row for the insertion)? That way the "first blank line after the list" becomes my target insert point/row.

 

[sfvb]

Sub grandtotal()
Rows("2:2&quot.Select
Application.CutCopyMode = False
Selection.Cut
Range("C3&quot.Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1&quot.Select
Selection.Insert Shift:=xlDown
End Sub

 

[SkipVought]

Hi,

Here's the same thing with selecting a thing.

You just use range objects...

Sub grandtotal()
    Rows("2:2").Cut
    Range("C3").End(xlDown).Offset(1, 0).EntireRow.Insert Shift:=xlDown
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com