Excel VBA Insert Row: Examples and Safe Methods

VBA & macros · Updated November 21, 2026

To insert a worksheet row with Excel VBA, use Rows(5).Insert Shift:=xlDown; this inserts a blank row above row 5. Qualify Rows with the worksheet you intend to change, and save a copy of the workbook before running a macro because VBA changes cannot be undone with Ctrl+Z.

What the Insert Method Does

Excel’s Insert method adds cells and shifts existing cells to make room. When you call it on a complete worksheet row, Excel moves the existing rows below the insertion point down. For example, inserting at row 5 leaves rows 1 through 4 in place, makes a new row 5, and moves the old row 5 to row 6.

A worksheet row is different from a table row and from a group of cells. Choose the object that matches what you mean to add:

  • Entire worksheet row: use Rows(5).Insert or Range("A5").EntireRow.Insert.
  • Cells in a range: use Range("A5").Insert Shift:=xlDown to shift cells in that range rather than inserting a complete sheet row.
  • Excel table row: use the table’s ListRows.Add method so the new record belongs to the table.

For most requests phrased as “insert a row,” the first option is intended. An unqualified Rows(5) refers to the active sheet, however, so a macro can affect the wrong sheet if the user changes the selection. Explicit worksheet references make the result more dependable.

Insert a Row at a Fixed Position

In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, then choose Insert > Module if the workbook does not already have a module. Add a procedure such as the following, update the worksheet name and row number, and run it from the editor or the macro dialog.

Option Explicit

Sub InsertRowAboveFive()
    Dim ws As Worksheet

    ' Change Data to the name of the target worksheet.
    Set ws = ThisWorkbook.Worksheets("Data")

    ' Insert a complete row above worksheet row 5.
    ws.Rows(5).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub

ThisWorkbook means the workbook containing this VBA code. If the code is stored in an add-in or personal macro workbook but should change a different file, use a suitable workbook reference instead. The worksheet name must match its tab name exactly. The row number can be a literal, as above, or a variable calculated by the procedure.

The optional CopyOrigin argument specifies where Excel takes the inserted row’s formatting from. xlFormatFromLeftOrAbove uses the formatting above; xlFormatFromRightOrBelow is the other built-in choice. Omitting CopyOrigin lets Excel apply its default formatting behavior. Formatting is separate from cell contents: inserting a row does not mean that values from the row above are copied into the new row.

Insert Rows Relative to a Cell or Selection

To insert above a known cell, use its entire row. This makes the intention clear even though the reference names a single cell:

Option Explicit

Sub InsertAboveA5()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Data")
    ws.Range("A5").EntireRow.Insert Shift:=xlDown
End Sub

To insert above the active cell, a macro can use ActiveCell.EntireRow.Insert. That is convenient for a user-run macro, but it depends on which sheet and cell are active when the macro runs. If a procedure should always change a particular worksheet, reference that worksheet explicitly and use a known row number or cell instead.

Be careful not to confuse Range("A5").Insert with Range("A5").EntireRow.Insert. The first inserts cells and requires a shift direction such as xlDown or xlToRight; it does not by itself mean “insert a whole worksheet row.” The second inserts the entire row containing A5.

Insert Several Rows at Once

For a fixed number of consecutive blank rows, insert a row range. The endpoints are inclusive, so rows 5 through 7 add three rows above the current row 5:

Option Explicit

Sub InsertThreeRows()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Data")
    ws.Rows("5:7").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub

Inserting the block in one operation is generally clearer than repeating a one-row insertion several times. If you do insert rows individually in a loop, consider whether each insertion changes the later row numbers your loop intends to use. Inserting repeatedly at the same position can also reverse the order in which prepared records appear. For a block of new data, size the inserted range to the number of records and then write the data into it.

Add a Row to an Excel Table

An Excel table, created with Insert > Table or Ctrl+T on Windows, has its own rows. Use the table’s ListRows.Add method when the new record should be part of that table. A worksheet row insertion near a table is not a substitute for adding a table record; table-specific features such as structured references and calculated columns depend on the data remaining inside the table.

Option Explicit

Sub AddTableRecord()
    Dim ws As Worksheet
    Dim tbl As ListObject

    Set ws = ThisWorkbook.Worksheets("Data")
    Set tbl = ws.ListObjects("Sales")

    ' Add a new row at the end of the table.
    tbl.ListRows.Add
End Sub

Replace Sales with the table’s actual name. You can find or change a table name by selecting a cell in the table and using the Table Design tab. ListRows.Add adds a table row at the end by default; its optional Position argument can place a row at a specified position. The optional AlwaysInsert argument controls whether cells below the table are shifted when the table expands. Check nearby data before choosing that behavior.

Find the Next Row Before Inserting

A macro often needs to add a row based on existing data rather than a hard-coded position. The following example finds the last nonempty cell in column A and inserts one worksheet row below it. It assumes column A contains a continuous record identifier or another reliable value for every data row.

Option Explicit

Sub InsertAfterLastRecord()
    Dim ws As Worksheet
    Dim lastRow As Long

    Set ws = ThisWorkbook.Worksheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Insert below the last nonempty cell found in column A.
    ws.Rows(lastRow + 1).Insert Shift:=xlDown
End Sub

The End(xlUp) technique starts at the bottom of column A and moves upward to the next nonempty cell, much like pressing Ctrl+Up from the bottom of a column. If column A has gaps, it still finds the last nonempty cell, but it does not identify the first gap in the middle of the data. If the column is empty, the expression returns row 1, so this example inserts at row 2. Adjust that case if an empty sheet should behave differently.

Choose the search column carefully. If records can have a blank in column A, use a column that is always populated or a more deliberate last-used-row strategy. A worksheet’s UsedRange can include cells that were formatted or edited in the past, so it is not always a reliable measure of the current last record.

Formatting, Formulas, and References

Inserting a complete row usually causes Excel to adjust many formulas that refer to shifted cells. For example, inserting a row inside a summed range will often expand the range. That behavior is useful but should not be treated as a guarantee for every kind of reference: text-based references used by functions such as INDIRECT, external links, and formulas constructed by code may need separate attention.

New rows can inherit formatting from a neighboring row. If the result looks wrong, specify CopyOrigin or clear the new row’s formatting after insertion with a suitable ClearFormats call. Clearing formats also removes useful number formats, borders, and styles, so do it only when that is the intended result. In a table, table styles and calculated columns may provide more predictable formatting than inserting a normal worksheet row next to the table.

Inserting a row does not automatically make it a copied record. If the new row needs formulas or values, assign them explicitly after insertion. For example, code can set a formula in a particular cell or copy selected cells from a template row. Avoid copying an entire row blindly when it might also copy old identifiers, dates, or other values that should be unique.

Common Problems and Safe Running

  • The wrong sheet changes: qualify row references with a worksheet variable, rather than relying on the active sheet.
  • The code reports a subscript error: confirm the worksheet or table name is spelled exactly as it appears in Excel.
  • Only some cells move: use EntireRow.Insert or Rows(number).Insert when a complete sheet row is required.
  • A protected sheet refuses the change: check the sheet’s protection settings. A macro may need the sheet to be unprotected or set up with suitable permissions; do not assume protection can be bypassed safely.
  • Merged cells cause an error or an awkward layout: inspect merged areas around the insertion point. Merged cells can constrain insert operations, so redesigning the layout may be more reliable than inserting through them.
  • The macro cannot be undone: Excel’s normal Undo history does not reverse VBA macro actions. Save a separate copy before running unfamiliar code.

Macros must run in desktop Excel; Excel for the web can open a macro-enabled workbook but does not run VBA macros. Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm) if you need the code to remain in that file. On Windows, Alt+F11 opens the Visual Basic Editor and Alt+F8 opens the Macro dialog. In Excel for Mac, Option+F11 and Option+F8 are common equivalents; some keyboards require Fn as well. Menu availability and shortcuts can vary with keyboard settings, and VBA availability differs from Excel for the web.

Frequently Asked Questions

How do I insert a row above the active cell in Excel VBA?
Use ActiveCell.EntireRow.Insert Shift:=xlDown. It inserts above the active cell’s row, so make sure the intended worksheet and cell are active before running it.

How do I insert a row at the bottom of my data?
Find the last nonempty cell in a dependable data column with Cells(Rows.Count, column).End(xlUp).Row, then insert at the next row. The method depends on that column being populated for each record.

How do I insert a row into an Excel table with VBA?
Reference the table as a ListObject and call ListRows.Add. This adds a table record, unlike inserting an unrelated worksheet row.

Can I undo a VBA row insertion with Ctrl+Z?
No. Running VBA generally clears Excel’s ordinary Undo history, and Ctrl+Z cannot reverse the macro’s row insertion. Save a copy before running code you may need to roll back.

Will inserting a row copy the row above?
No. Insertion creates space and may copy formatting according to Excel’s insertion behavior or the CopyOrigin setting. It does not copy the previous row’s contents as a new record.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.