Excel VBA Delete 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. Read the code once before running it: note which sheets, ranges and files it touches and whether it deletes or overwrites anything. Macros run only in desktop Excel with macros enabled, and the code is kept only in a macro-enabled format such as .xlsm. Treat “excel vba delete row” as a small repeatable workflow rather than a one-off click you hope to remember next time. Use a small test block before the live file, so any surprise in the affected rows shows up while it is still harmless. 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 turns a workflow that saves repeating the same clicks every week into a method you can reuse, explain, and defend when the workbook leaves your screen.

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 the macro will be shared, add a comment at the top saying what it changes and which sheet it expects, and keep the workbook as .xlsm — saving it as .xlsx removes the code.

In Google Sheets

Google Sheets does not run VBA. A macro written for Excel has to be rewritten in Google Apps Script (Extensions ▸ Apps Script), and an .xlsm file converted to Sheets keeps its cells but drops the code. Keep this page bookmarked for the next time the same question comes up. Better still, repeat the steps once in your own workbook — doing it yourself is what turns a copied answer into something you remember. For “excel vba delete row”, the checks are about the environment as much as the code: desktop Excel with macros enabled for this file, a macro-enabled workbook and a saved copy to fall back on. Run it once on test data, compare the result with what the code says it does, and only then run it on the real sheet.

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