TL;DR — Inserting is the mirror image of deleting.
.EntireRow.Insertdoesn't conjure empty space out of nothing — it shoves every existing row down to open a gap, exactly the way deleting slides them up. That means the same grid-moves-under-you problem applies: insert inside a top-down loop and the rows you haven't reached keep sliding away from you. Insert a whole row with.EntireRow.Insert; on a partial range, name the direction withShift:=xlDown(orxlToRight); control which neighbour's formatting the new row copies withCopyOrigin; and to add many rows, insert a multi-row range in one call instead of looping.
' Insert a blank row ABOVE row 5, pushing 5, 6, 7... down by one.
Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Data")
ws.Rows(5).EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Once you've met the delete-rows trap — a forward loop that skips rows because deleting slides the grid up — inserting holds no surprises, because it's the same idea running the other direction. Insert pushes the grid down. Everything that made deleting subtle (the numbers move, whole-row vs partial, doing it once vs in a loop) has an exact insert-side counterpart. This guide walks them in that mirror, so the model you already have does most of the work.
What you'll learn
- The mental model — inserting pushes the grid down, it doesn't fill empty space
- Whole-row insert versus a partial range, and why
Shiftdecides the direction CopyOrigin— which neighbour's formatting the inserted row or column inherits- Inserting inside a loop without re-processing your own new rows
- The one-call way to insert many rows at once
The mental model: inserting pushes the grid down
Just as deleting a row removes it and closes the gap, inserting a row opens a gap and
pushes everything down to make room. ws.Rows(5).EntireRow.Insert doesn't write into row 5 —
it makes a brand-new empty row 5, and the old row 5 becomes row 6, the old 6 becomes 7, and so
on all the way to the bottom of the used range. The new row arrives; every existing row below
the insertion point moves down by one.
This is the single fact to hold onto, because it's the source of both the everyday behaviour
and the loop trap. Insert "above row 5" and you're really saying "make room at 5 and slide the
rest down." Columns work identically sideways: Columns("C").Insert opens a new column C and
pushes D, E, F to the right. The grid is elastic, and Insert stretches it — which means the
address of everything below (or right of) the insertion point just changed.
The rule for partial ranges: name the direction with Shift
When you insert a whole row or column, there's no ambiguity — a whole row can only go down, a whole column can only go right. But insert a partial range and Excel has to guess which way to push the neighbours, and its guess is based on the range's shape:
ws.Range("B5:D5").Insert Shift:=xlDown ' push B:D down, leave columns A and E alone
ws.Range("B5:B8").Insert Shift:=xlToRight ' push these cells right
Leave Shift off a partial range and Excel picks a direction from whether the block is wider
than it is tall — a rule nobody remembers, which is how you end up with cells shoved sideways
when you meant them to go down. Whenever the range isn't a full row or column, state Shift
explicitly. And most of the time you don't want a partial insert at all: shifting a
sub-block of cells misaligns every column it doesn't include, the same corruption a
single-cell delete causes. Reach for .EntireRow.Insert / .EntireColumn.Insert unless you
genuinely mean to move a block:
ws.Rows(5).EntireRow.Insert ' whole row — every column stays in step
ws.Columns("C").EntireColumn.Insert ' whole column — every row stays in step
The rule that surprises people: CopyOrigin decides the formatting
A freshly inserted row isn't blank of formatting — Excel copies the look of a neighbour, and
CopyOrigin chooses which one. The default is xlFormatFromLeftOrAbove, so a new row inherits
the format of the row above it. Usually fine — until the row above is a bold, shaded header,
and your new data row arrives dressed as a header too:
' New row copies the format of the row BELOW instead of the header above.
ws.Rows(2).EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
xlFormatFromRightOrBelow tells Excel to take formatting from the row beneath the insertion
point — exactly what you want when you're inserting a data row directly under a header. This is
a small flag with a visible result: get it wrong and every inserted row wears the wrong
clothes; get it right and inserts look native. If you want a genuinely clean row, insert first,
then clear the formats explicitly with .ClearFormats.
The loop trap, mirrored: insert backwards or re-process your own rows
Inserting in a loop has the same hazard as deleting in a loop, pointed the opposite way. Suppose you scan down and insert a blank separator row after every "Total" row. Insert while moving top-down and the new row pushes your unscanned rows further down — and depending on how you advance, the loop can walk straight into the row it just created and insert again, and again. The grid grows under you.
The clean fix is the same as delete's: loop from the bottom up, so every insert happens below the rows you still have to visit and never disturbs their numbers:
Dim i As Long, lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
For i = lastRow To 2 Step -1
If ws.Cells(i, "A").Value = "Total" Then
ws.Rows(i + 1).EntireRow.Insert Shift:=xlDown ' insert below; rows above are untouched
End If
Next i
Going backwards, each inserted row lands beneath cells you've already handled, so the rows
still waiting keep their addresses. It's the identical discipline from
VBA Delete Rows — when a loop changes the number of rows, walk it
Step -1.
Inserting many rows at once: resize, don't repeat
Calling .Insert a hundred times to add a hundred rows is slow — each call restructures the
grid and recalculates. To insert n contiguous rows, insert an n-row range in a single
operation. Grab the target row, .Resize(n) it to the height you want, and insert that:
' Insert 5 blank rows above row 10 in one call.
ws.Rows(10).Resize(5).EntireRow.Insert Shift:=xlDown
Rows(10).Resize(5) is rows 10 through 14; inserting that block opens five rows at once and
pushes the rest down together. One structural change instead of five. For scattered inserts
across a large sheet, the fastest pattern is often to add a helper column, sort or filter to
group the rows, and insert in bulk — the same "do the structural edit once" principle that
makes bulk deletes fast. Wrap big inserts in Application.ScreenUpdating = False and manual
calculation and they finish instantly.
How ExcelMaster helps
Insert code fails in quiet, cosmetic ways: a partial range shoved the wrong direction because
Shift was omitted, new rows wearing a header's bold formatting, a top-down loop that inserts
into its own output, a hundred separate inserts that make a report crawl. Each one looks like
"the insert didn't quite work" rather than a clear error.
ExcelMaster lets you say
what you want — "add a blank row after every subtotal," "insert three rows above the summary,"
"put a new column between C and D." It writes .EntireRow.Insert (or .EntireColumn.Insert)
with the right Shift and CopyOrigin, loops in the safe direction, batches multi-row inserts
into one call, and backs up the sheet first. You keep the workbook and the code; you skip the
guesswork about which way the grid is about to move.
Frequently asked questions
How do I insert a row in VBA?
Use .EntireRow.Insert on the row you want to push down: ws.Rows(5).EntireRow.Insert creates
a new blank row 5 and moves the old row 5 and everything below it down by one. Add
Shift:=xlDown for clarity and CopyOrigin to control which neighbour's formatting the new
row inherits.
What does the Shift argument do when inserting?
Shift tells Excel which way to push existing cells when you insert a partial range —
xlDown moves them down, xlToRight moves them right. It's unnecessary for a whole row or
column (those can only go one way) but important for a sub-block, where Excel otherwise guesses
the direction from the range's shape.
Why does my inserted row have the wrong formatting?
Inserted rows copy a neighbour's format, chosen by CopyOrigin. The default
xlFormatFromLeftOrAbove copies the row above — so inserting under a header makes the new row
look like a header. Pass CopyOrigin:=xlFormatFromRightOrBelow to copy the row below instead,
or call .ClearFormats on the new row after inserting.
How do I insert multiple rows at once in VBA?
Resize the target row to the count you need and insert that block in one call:
ws.Rows(10).Resize(5).EntireRow.Insert inserts five rows above row 10. A single multi-row
insert is much faster than calling .Insert five times, because Excel restructures the grid
only once.
Should I loop forwards or backwards when inserting rows?
Backwards, for the same reason as deleting. Inserting pushes unscanned rows further down, and a
top-down loop can end up inserting into the rows it just created. Looping
For i = lastRow To 2 Step -1 keeps every insert below the cells you still have to process, so
their row numbers never shift.
Tested in
Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-08-07.
Related guides: VBA Delete Rows · VBA Hide Columns and Rows · VBA Last Row · VBA Range · VBA For Loop