Excel VBA: Insert a Row and Shift Cells Down

To insert a worksheet row in Excel VBA and push the existing rows down, use Rows(rowNumber).Insert Shift:=xlShiftDown on the worksheet you want to change. For example, Worksheets("Data").Rows(5).Insert Shift:=xlShiftDown creates a blank row 5 and moves the old row 5 and everything below it down one row.

The basic VBA syntax

The Insert method adds cells to a range. Its Shift argument tells Excel which direction to move existing cells: xlShiftDown moves them down, while xlShiftToRight moves them right. When the range is a complete worksheet row, shifting down is the intended result. A clear, fully qualified example is:

Sub InsertRowAtFive()
    Dim ws As Worksheet

    ' Target the intended sheet, not whichever sheet happens to be active.
    Set ws = ThisWorkbook.Worksheets("Data")
    ws.Rows(5).Insert Shift:=xlShiftDown
End Sub

Change "Data" to the worksheet’s tab name and change 5 to the row number where the new row should appear. The number refers to the position of the new row, not the row that will remain there: after the insertion, the old row 5 becomes row 6.

The optional CopyOrigin argument controls where Excel takes the inserted row’s formatting from. For example, CopyOrigin:=xlFormatFromLeftOrAbove generally takes formatting from above for a row insertion. It concerns formatting, not a direction to shift cells; Shift:=xlShiftDown is the part that specifies the movement.

Insert a whole row or only shift a range?

Choose the range carefully. ws.Rows(5).Insert inserts an entire worksheet row, so cells in every column move down. This is usually the right choice when the worksheet contains a continuous table of related records across multiple columns. It keeps a record together rather than moving only some of its fields.

You can instead insert cells in a limited range. For example, ws.Range("B5:D5").Insert Shift:=xlShiftDown inserts cells in columns B through D and shifts cells below that range down in those columns. Data in columns A and E is not shifted with them. That can be useful when the columns are independent, but it can split records or make rows misaligned if each worksheet row represents one complete record.

Use a whole-row insertion when names, dates, amounts, and other fields in the same record must stay together. Use a partial-range insertion only when you deliberately want to affect a subset of columns and have checked what sits beside it. Inserting cells into a range that intersects merged cells can also trigger errors or unexpected layouts, so inspect merged areas before using a partial-range approach.

Insert above the active cell

If the desired insertion point depends on where the user has selected, use the active cell’s row number. The following macro inserts a complete worksheet row above the active cell on the active sheet:

Sub InsertRowAboveActiveCell()
    Dim ws As Worksheet
    Dim insertAt As Long

    ' Use the active cell's row as the insertion point.
    Set ws = ActiveSheet
    insertAt = ActiveCell.Row
    ws.Rows(insertAt).Insert Shift:=xlShiftDown
End Sub

This is convenient for a macro assigned to a button, but it depends on the user being on the intended sheet and selecting the intended row first. For a repeatable automated task, naming the worksheet and calculating the row from a known marker is safer than relying on the active selection. Also remember that Excel row numbers are worksheet positions; they are not the same thing as record numbers in an Excel table.

Choose the insertion point from data

A common task is to add a row after the last populated item in a known column. One approach is to find the last used cell in that column, then insert below it. This example assumes row 1 contains headings and column A is populated for every record:

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

    ' Column A identifies the occupied records; row 1 is the header.
    Set ws = ThisWorkbook.Worksheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    insertAt = lastRow + 1
    ws.Rows(insertAt).Insert Shift:=xlShiftDown
End Sub

If column A contains only a heading, this calculation returns row 2 as the insertion point, which is appropriate for a sheet with a header and no records. If column A has gaps, however, the last populated cell may not describe the actual extent of the data in other columns. Pick a column that is reliably filled, or use an Excel table when the records form a table. If the sheet has no header, adjust the assumptions and starting position accordingly.

Insert several rows

To create several blank rows at one location, insert a multirow range. For example, ws.Rows("5:7").Insert Shift:=xlShiftDown inserts three rows starting at row 5. The previous row 5 and the rows beneath it move down by three positions. This is usually simpler and faster than running three separate insert statements.

When inserting rows for many matching records in a loop, process the worksheet from the bottom upward. Inserting near the top changes the row numbers of records below it; a top-to-bottom loop can therefore revisit records or skip them. A bottom-up loop avoids that particular shifting problem:

Sub InsertRowsForMarkedRecords()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim r As Long

    ' Check column A and insert above rows marked Add in column B.
    Set ws = ThisWorkbook.Worksheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    For r = lastRow To 2 Step -1
        If ws.Cells(r, "B").Value = "Add" Then
            ws.Rows(r).Insert Shift:=xlShiftDown
        End If
    Next r
End Sub

This example inserts a blank row above each row whose column B value is exactly Add; it does not copy that record into the new row or remove the marker. Adapt the condition and resulting actions to the task. For large datasets, repeated row insertions can take time and may cause formulas or formatting to be recalculated often. Consider building the final data in an array or a separate range when performance becomes an issue.

What happens to formatting, formulas, and references?

Excel adjusts many worksheet references when rows are inserted. For instance, formulas that refer to cells displaced by a new row are often updated to continue referring to the intended cells. The exact outcome depends on the formula, the insertion location, and whether references are relative, absolute, or part of a named range. Review important formulas and dependent sheets after a macro changes the layout, especially when other workbooks or external links refer to the sheet.

Formatting is separate from formula behavior. If you specify CopyOrigin, Excel uses the adjacent side you choose as the source of formatting for the inserted cells. It does not mean that the new row is guaranteed to inherit the formulas or values above. If a plain unformatted row is required, insert it and then clear the new row’s formats deliberately; be careful to clear only the inserted row, not its values or neighboring cells. For a structured Excel table, table behavior is usually preferable because calculated columns and table formatting can expand according to the table’s rules.

Adding a row inside an Excel table

An Excel table, also called a ListObject in VBA, has its own row collection. If the records are inside a table, use ListRows.Add rather than treating a table row as an ordinary worksheet row. The position passed to ListRows.Add is the row’s position within the table’s data rows; it is not a worksheet row number.

Sub AddRowToTable()
    Dim ws As Worksheet
    Dim lo As ListObject
    Dim addedRow As ListRow

    ' Insert the new row as the second data row in the table.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set lo = ws.ListObjects("SalesTable")
    Set addedRow = lo.ListRows.Add(Position:=2, AlwaysInsert:=True)
End Sub

Replace SalesTable with the table’s actual name. Setting AlwaysInsert:=True tells Excel to shift cells below the table down when necessary to make room. If the intention is to append a record, omit the position argument and use the table’s normal add-row behavior instead. Adding through the table helps preserve its structure, including table formatting and calculated-column behavior, though the exact result still depends on the table’s existing setup.

Common problems and how to avoid them

The wrong sheet changes: An unqualified statement such as Rows(5).Insert applies to the active sheet. Assign a worksheet to a variable and call ws.Rows(5).Insert to make the target explicit.
Only some columns move: A range such as B5:D5 shifts cells only within that selected range. Use Rows(5) when the whole record must move together.
The row is inserted in the wrong place: Inserting at row 5 places a blank row before the old row 5. If you mean after the old row 5, insert at row 6.
A macro fails on a protected sheet: Worksheet protection can prevent insertion. Check the sheet’s protection state and the workbook’s intended protection procedure before running the macro; do not assume that protection can be bypassed.
Unexpected results near merged cells or filtered data: Merged cells and filtered ranges can complicate row operations. Check the layout first, and test the macro on a copy with the same structure.
Formatting looks different: Review CopyOrigin and the formatting of adjacent rows. The insertion command does not guarantee that the new row will look exactly as intended in every layout.

VBA compared with inserting a row manually

For a one-time change, using Excel’s interface is often quicker. On Windows, select a worksheet row and press Ctrl+Shift++ (Ctrl and Shift with the plus key) to insert a row; the Insert dialog may appear if you have selected cells rather than an entire row. You can also use Home > Insert > Insert Sheet Rows. On Mac, the equivalent shortcut can depend on the keyboard layout; Control+Shift+= is commonly used, and the Home > Insert command is an alternative when the shortcut differs.

VBA is more useful when the same insertion must happen repeatedly, when the row position is calculated from data, or when the operation belongs in a larger process. A macro can also make the target sheet and insertion rule explicit. Neither method removes the need to check the result: inserting rows changes the layout and can affect formulas, charts, named ranges, print areas, and references in other parts of a workbook.

Save and run the macro safely

Before running a macro that changes rows, save a copy of the workbook. Excel macros generally cannot be undone with Ctrl+Z, so saving first gives you a reliable way back if the insertion rule was wrong. If you need to preserve VBA code in the workbook, save it in a macro-enabled format such as .xlsm; a standard .xlsx file does not retain VBA macros.

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, add the code to a standard module, and run the procedure from the editor or from Excel’s macro tools. Macro security settings may prevent code from running, particularly if the file came from an untrusted source. Only enable macros when you trust the workbook and understand what the code does. Excel for the web can open workbooks containing macros, but it does not run VBA; use the desktop Excel app to execute these procedures.

Frequently Asked Questions

What is the VBA command to insert a row and shift cells down? Use Rows(rowNumber).Insert Shift:=xlShiftDown on the target worksheet. For example, ws.Rows(5).Insert Shift:=xlShiftDown inserts a complete worksheet row at row 5 and shifts existing rows below it down.

How do I insert a row above the current row? Use the active cell’s row number as the insertion position: ActiveSheet.Rows(ActiveCell.Row).Insert Shift:=xlShiftDown. For a more dependable macro, assign the intended worksheet to a variable instead of relying on whichever sheet is active.

How do I insert a row without shifting every worksheet column? Insert cells in a limited range, such as ws.Range("B5:D5").Insert Shift:=xlShiftDown. Only that range and cells below it in those columns are shifted, so use this only when the surrounding columns should remain in place.

How do I add a row to an Excel table with VBA? Get the table as a ListObject and call ListRows.Add. Use its Position argument to specify a position within the table’s data rows, or omit that argument to add at the end.

Can I undo a VBA row insertion with Ctrl+Z? Do not count on it. Running VBA code generally clears Excel’s normal undo history, so save a copy before executing a macro that inserts or moves rows.

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.