Macro to Insert Rows in Excel

These VBA macros insert any number of rows above or below your selection in one step.

Works inExcel 2010 and later, Windows and Mac
UndoNot possible. Save a copy first
Save asMacro-Enabled Workbook (.xlsm)

New to macros? See how to use the macros on this site: copy the code, paste it into a module with Alt + F11, and run it with Alt + F8.

Insert rows above the active cell

Sub InsertRowsAbove()
    Dim n As Long
    n = Application.InputBox("How many rows to insert?", Type:=1)
    If n < 1 Then Exit Sub
    ActiveCell.EntireRow.Resize(n).Insert
End Sub

The new rows take the formatting of the row above, just like inserting by hand.

Insert rows below the selection

Sub InsertRowsBelow()
    Dim n As Long
    n = Application.InputBox("How many rows to insert?", Type:=1)
    If n < 1 Then Exit Sub
    Selection.Offset(Selection.Rows.Count).EntireRow.Resize(n).Insert
End Sub

Without a macro

Do this To
Select 5 rows, then Ctrl + + Insert 5 rows at once
Shift + Space, then Ctrl + + Select the row and insert one above
F4 after inserting Repeat the insert

Things to know

  • Formulas that refer to a range below the inserted rows adjust automatically.
  • Inserting rows inside an Excel Table adds them to the table.
  • Rows can’t be inserted if the last rows of the sheet contain data, because there’s nowhere for it to move.

Related macros