Excel VBA Insert Row

What this does

Inserting and deleting whole rows is a one-line operation in VBA, and the subtleties are all about what happens to everything else. Insert always places the new row above the referenced one and shifts the existing content down, so Rows(2).Insert makes the new blank row number 2. The CopyOrigin argument decides which neighbour the new row inherits formatting from — xlFormatFromLeftOrAbove is the default and usually what you want; xlFormatFromRightOrBelow is there for tables formatted the other way. Inserting several rows at once is a single call on a row range: Rows("10:12").Insert adds three. Deletion has the harder problem, which appears only inside loops: each Delete renumbers every row beneath it, so a loop that counts upwards processes row 5, deletes it, and then finds what used to be row 6 sitting at position 5 while the counter has already moved to 6 — every second match is skipped. Counting down with Step -1 removes the problem entirely, because deletions only affect rows the loop has already passed. The same idea underpins a lot of everyday Excel work, so the few minutes spent getting it right here pay back across every sheet you build afterwards. Treat it as a pattern, not a one-off, and it stops being something you look up and starts being something you reach for. For “excel vba insert row”, the reliable version is a short checking loop, not just the first command that appears to work. Run it on a deliberately small range first, watch how the affected rows change, and only then apply the same setup to the full sheet. When this is a ribbon command, the selection matters more than the button: confirm the range, apply the command, then spot-check the output before saving. That is what makes a workflow that saves repeating the same clicks every week useful in real work: repeatable, auditable, and not dependent on memory or luck.

A worked example

Sub InsertAndDeleteRows() shows all three shapes: a single insert that inherits the formatting above it and stamps a date into the new row, a block insert of three rows in one call, and a delete that pulls the rows below back up. The loop case is where the care is needed. To delete every row whose status column reads "closed", find the last row first, then iterate For i = lastRow To 2 Step -1 and delete inside the If. Written forwards, exactly half the closed rows survive — and the failure is quiet, because the macro reports no error and simply leaves rows behind. If several scattered rows must go, an alternative to looping is to collect them into one Range as you scan and delete that in a single call at the end, which is both faster and immune to the renumbering problem. Union cannot be seeded with an empty variable, so the first row has to be handled separately: If toKill Is Nothing Then Set toKill = ws.Rows(i) Else Set toKill = Union(toKill, ws.Rows(i)). Passing a Nothing object to Union raises error 91 on the very first match, which is the usual reason this pattern fails the moment it is copied out of an article. Finish with If Not toKill Is Nothing Then toKill.EntireRow.Delete. Row insertion and deletion is the backbone of data cleaning, and it is also the place where a macro can destroy work silently. The two habits that prevent it — loop backwards, test on a copy — cost nothing and remove the entire class of bug. If there is any chance you will reuse this, drop it into a small template tab right now: a labelled input area on the left and the formula beside it, checked once against the tool above. Next time the same question comes up, the answer is a single paste away instead of a rebuild from memory.

In Google Sheets

Everything above works in Google Sheets too. Excel and Sheets share the formula syntax used here; only the surrounding menus are arranged differently. That portability is deliberate — learn it once and it follows you between the two tools and across Windows and Mac. The aim was to get you unstuck fast and leave you a little more capable than a copy-paste would. The answer is at the top, the tool proves it, and the detail above shows why it holds — so the next time a colleague asks, you can answer without reaching for search. Treat “excel vba insert row” as a small building block rather than a chore. Once the inputs sit in their own cells and the formula reads from them, the same setup answers a dozen related questions with a tweak, and Excel keeps every dependent figure current as the data changes. The tool above is there so you can rehearse and verify before committing anything to a real workbook; the steps and worked example are there so the logic sticks. Get it right once and it stops costing you time — it starts saving it, every time the question comes back around.

Common mistakes

• Deleting inside a forward loop. Every deletion moves the rows below up while the counter advances, so consecutive matches are skipped and roughly half the intended rows survive.
• Reading the last row once and then trusting it after inserting. Insertions push the last row further down, so a stored lastRow goes stale — recompute it, or work backwards.
• Using ws.Rows(5).Clear when the row should be removed. Clear empties the cells but leaves the row in place, so the gap stays and every row number below is unchanged.
• Deleting a row that a formula elsewhere refers to. The dependent formulas become #REF! immediately and there is no undo after a macro, so check dependencies before running it on live data.
• Inserting row by row in a loop when a block would do. Rows("10:12").Insert is one operation; three separate inserts are three, and each one shifts the sheet again.

Frequently asked questions