VBA insert 5 rows above the last row

[armagan56]

1 1
2 3
3 2
4
5 6



Hi I have a spreadsheet with a total on the last row, I want a macro to insert 5 blank rows above the total row (listed at row 5 in the above example) at the bottom.

the trouble is of course as soon as you insert 5 rows, then next time I need to run this, the macro will need to detect which is the total row again before inserting another 5 rows.

any help/advice would be appreciated.

 

[njimack]

Sub Insert_Rows()

Application.ScreenUpdating = False

Dim Last_Row As Long

With Sheets("Sheet1")

    Last_Row = Range("A" & Rows.Count).End(xlUp).Row
    
    Range(.Cells(Last_Row, 1), .Cells(Last_Row + 4, 1)).EntireRow.Insert

End With

Application.ScreenUpdating = True
End Sub

 

[jim may]

Possibly... (if I understand)

Sub Foo()
RATR = Range("A" & Rows.Count).End(xlUp).Row - 1   ' Get the Row number just above the current Total Row
Range("A" & RATR).Select
ActiveCell.Resize(5).EntireRow.Insert shift:=xlDown
End Sub

 

[armagan56]

thanks guys, so simple yet was so confusing!