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. A macro runs in the desktop app's VBA environment, not in the cell grid: the workbook has to be saved in a macro-enabled format such as .xlsm to keep the code, and your macro security settings — or a file that arrived from the internet — can block it until you trust it. Excel for the web cannot run VBA at all. 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 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. 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 and the detail above shows why it holds — so the next time a colleague asks, you can answer without reaching for search. For “excel vba insert 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