How to insert row at the end of a range in Excel with VBA

Question

I'm trying to insert a row at the bottom of the range, but nothing happens when I run the below code. If I remove the "1" in

Cells(nextRow, 1).EntireRow.Insert

It will insert a row at the top of the range.

Sub newRow()

Application.ScreenUpdating = False
 Sheet1.Activate

'goes to the row at the bottom of the range
nextRow = Cells(Rows.Count, 1).End(xlUp).row + 1

'inserts new row
Cells(nextRow, 1).EntireRow.Insert

Application.ScreenUpdating = True

End Sub

If you are already at the bottom where the blank rows are, why are you inserting a new row? What are you expecting to move down? — braX, Nov 22 '19 at 15:04
https://stackoverflow.com/questions/20741836/how-to-select-the-lastrow — Robert Harvey, Nov 22 '19 at 15:04

score 2 · Accepted Answer · answered Nov 22 '19 at 15:12

Inserting a row at the bottom of the range is not visible - and it is an empty row, which is not different than the unused empty rows. Anyway, inserting a row before the last one of the range is quite visible:

Sub NewRow()

    Dim nextRow As Long

    With Sheet1
        nextRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        .Cells(nextRow, 1).EntireRow.Insert
    End With

End Sub

Usin With Sheet1 and .Cells() instead of Select and Activate is a good practice -How to avoid using Select in Excel VBA

How to insert row at the end of a range in Excel with VBA

1 Answers1