VBA Range.Insert: Insert Cells, Rows, and Columns in Excel

In VBA, use the Range.Insert method to add cells, rows, or columns and shift existing content out of the way. For example, ws.Rows(5).Insert inserts a worksheet row, while ws.Range("B2:C3").Insert Shift:=xlShiftToRight inserts cells and moves existing cells to the right. Qualify the range with its worksheet so the code changes the intended sheet.

What Range.Insert does

Range.Insert adds space to a worksheet by shifting existing cells. The range you specify determines the size and shape of the insertion. If you specify a rectangular cell range, Excel inserts cells of that shape; if you specify entire rows or columns, Excel inserts full worksheet rows or columns. Existing values and formulas are displaced, not overwritten.

For example, inserting a two-cell vertical range above A4 shifts the cells below A4 down by two positions in that column. Inserting a whole row at row 4 shifts every row from 4 downward. Those are different operations: a cell insertion can leave other columns in place, while a row insertion moves content across the entire worksheet row.

Insertion also affects worksheet structure. Excel normally adjusts formulas and references that point to cells affected by the new space. Formatting can be carried into the inserted cells, and merged cells, protected sheets, tables, or other worksheet features can affect whether an insertion succeeds as expected.

Syntax and the Shift argument

The method’s general form is expression.Insert(Shift, CopyOrigin). Both arguments are optional. In a VBA statement, you can use named arguments to make the intended behavior clear:

Shift:=xlShiftDown moves existing cells down to make room.
Shift:=xlShiftToRight moves existing cells right to make room.
CopyOrigin:=xlFormatFromLeftOrAbove takes formatting from the cell to the left or above the inserted range.
CopyOrigin:=xlFormatFromRightOrBelow takes formatting from the cell to the right or below it.

For a rectangular cell range, set Shift explicitly when you need predictable behavior. If you omit it, Excel determines the shift direction from the range’s shape. For a whole row or column, the direction is inherent in the range, so the insertion is unambiguous. CopyOrigin controls formatting, not which cells move.

Insert a cell range

Use a cell address when only part of the worksheet should move. This example inserts a two-row by two-column area at B2 and shifts existing cells to the right. Change the worksheet name to match the workbook:

Sub InsertCellsToRight()
    Dim ws As Worksheet

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

    ' Insert a 2-by-2 cell area and move existing cells right.
    ws.Range("B2:C3").Insert Shift:=xlShiftToRight
End Sub

The inserted area has the same dimensions as B2:C3. To move cells down instead, use Shift:=xlShiftDown. Pick the direction based on the layout: shifting right is useful for adding fields within a record, while shifting down can create space within a column or block. A partial-range insertion can make rows uneven, so check neighboring columns before using it in a structured data list.

Insert entire rows or columns

For a full worksheet row, use the row range or a range’s EntireRow property. The following macro inserts one row at row 5:

Sub InsertWorksheetRow()
    Dim ws As Worksheet

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

    ' Insert a complete worksheet row at row 5.
    ws.Rows(5).Insert
End Sub

To add several consecutive rows, specify a row range, such as ws.Rows("5:7").Insert. Excel inserts three rows starting at row 5. To insert a column, use a column range such as ws.Columns("C:C").Insert; this adds a complete column C and shifts the existing columns to its right. You can also start with a cell range and use ws.Range("B5").EntireRow.Insert or ws.Range("C1").EntireColumn.Insert.

These forms make the scope explicit. Avoid bare expressions such as Rows(5).Insert in reusable code: without a worksheet qualifier, VBA applies the operation to the active sheet, which may not be the sheet you intended.

Choose the right insertion method

Before inserting, decide whether the task needs cells, a complete worksheet row, or a row inside an Excel table. These choices are not interchangeable. Use:

A cell range when only a defined block should move and the rest of each row must stay in place.
EntireRow when all columns in the worksheet need to move down together.
EntireColumn when all rows need a new field or column.
A table row operation when the data is an Excel Table and the new record should remain part of that table.

For a table named SalesTable, add a table row with the table’s ListRows.Add method instead of inserting an arbitrary worksheet row inside the table. This keeps the operation tied to the table object and is generally more appropriate when the goal is to add a record. A worksheet row insertion may also expand a table depending on where it occurs, but using the table method states your intent more clearly.

Formatting and CopyOrigin

Inserted cells need a format, and Excel uses a neighboring cell as the source. You can choose which side or direction supplies that format with CopyOrigin. For example, to insert cells at B2 and use the format from the cell to the right or below, write ws.Range("B2:C3").Insert Shift:=xlShiftToRight, CopyOrigin:=xlFormatFromRightOrBelow.

There is no CopyOrigin value that means “insert with no formatting.” If you need to remove formatting after insertion, clear it separately, or apply a known format from a template range. Be careful with ClearFormats: it removes formatting but leaves values and formulas intact. Formatting choices matter when the neighboring cells use different number formats, borders, fills, or conditional formatting.

Run macros carefully

Before running any insertion macro, save a copy of the workbook. VBA changes cannot be reversed with Excel’s Undo command (Ctrl+Z on Windows), so a mistaken insertion may require closing without saving or restoring a backup. Review the target worksheet name and range before execution, especially if the macro will run on a workbook with important formulas or data.

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, add code in a standard module, and run a procedure from the editor. On a Mac, Option+F11 opens the editor on many keyboards; the Fn key may also be needed depending on keyboard settings. Macro availability and security prompts depend on the workbook and Excel’s security settings. Run macros only when you trust their source, and save macro-enabled workbooks in a format that supports VBA, such as .xlsm.

Common problems and fixes

If the code inserts on the wrong sheet, qualify every relevant range with a worksheet variable, as in the examples. ThisWorkbook refers to the workbook containing the macro; ActiveWorkbook refers to the workbook currently active in Excel. Choose deliberately when code may operate across multiple open workbooks.

“Cannot shift objects off sheet” or a similar error: Objects, formatting, or data near the worksheet edge may prevent Excel from shifting cells. Inspect the far-right columns or bottom rows and remove unintended content or objects only after confirming they are not needed.
Protected worksheet: A protected sheet may block insertion. Review the sheet’s protection settings or have the macro handle protection appropriately; do not remove protection without permission.
Merged cells: A range that intersects merged cells can produce errors or unexpected results. Unmerge or redesign the affected area if appropriate, then test on a copy.
Formulas or named ranges change: Excel may update references when cells are inserted. Inspect dependent formulas, defined names, charts, and references in other sheets after the change.
Partial data shifts out of alignment: A cell-range insertion moves only the selected region. If each record spans multiple columns, insert a full row to keep the record together.

Frequently Asked Questions

How do I insert a row with VBA?
Qualify the worksheet and use ws.Rows(5).Insert to insert a complete row at row 5. For multiple rows, use a range such as ws.Rows("5:7").Insert.

How do I insert cells and shift them down?
Specify the cell range and use Shift:=xlShiftDown, for example ws.Range("B2:C3").Insert Shift:=xlShiftDown. The inserted block takes the dimensions of the specified range.

What is the difference between Range.Insert and Rows.Insert?
Range.Insert can insert a partial block of cells or a whole row or column, depending on the range. Rows.Insert specifically inserts complete worksheet rows.

Can I insert a row into an Excel table?
Yes. When adding a table record, use the table’s ListRows.Add method. It targets the table itself rather than relying on a worksheet row insertion to expand it.

Can I undo a VBA insert?
Usually not with Excel’s Undo command. Save a backup before running the macro; if you have not saved the unwanted change, closing the workbook without saving may let you return to the prior saved version.

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.