In Excel: ws.Rows(5).Insert Shift:=xlDown inserts a blank row above row 5; ws.Rows(5).Delete Shift:=xlUp removes row 5 and closes the gap.
VBA macro: Insert and Delete Rows with VBA
Sub InsertAndDeleteRows()
Dim ws As Worksheet
Set ws = ActiveSheet
' Insert one row above row 2, inheriting formatting from the row above
ws.Rows(2).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
ws.Cells(2, 1).Value = "Inserted " & Format(Now, "yyyy-mm-dd")
' Insert three rows at once, above row 10
ws.Rows("10:12").Insert Shift:=xlDown
' Delete row 5 and pull everything below it up
ws.Rows(5).Delete Shift:=xlUp
End SubRows(2).Insert pushes the existing row 2 down, so the new blank row becomes row 2. Inserting a block is one call on a row range — "10:12" adds three rows in a single operation rather than three separate inserts.
How to run this macro
- Press Alt + F11 to open the VBA editor.
- Insert > Module.
- Paste the code above.
- Press
F5, or close the editor and run it from Developer > Macros. - Save the file as .xlsm so the macro is kept.
Reference the row by number or as a range of rows: ws.Rows(5) or ws.Rows("10:12").
Call .Insert Shift:=xlDown to add blank rows above that position, adding CopyOrigin if formatting should come from below instead.
Call .Delete Shift:=xlUp to remove rows and close the gap.
Inside a loop, find the last row first and iterate backwards with Step -1 so deletions cannot shift rows past the counter.
Test on a copy — deletion cannot be undone with Ctrl + Z once a macro has done it.
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. Keep the inputs visible and clearly labelled and the whole thing stays auditable — anyone who opens the file later, including you, can see at a glance exactly what feeds the result and change one assumption without hunting through the formula. 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 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
If you are in Google Sheets rather than Excel, the good news is that the formula shown here is identical and the workflow barely changes — menus sit across the top instead of in a ribbon, and a few function names differ slightly, but anything you build here moves across with little or no rework. Keep this page bookmarked for the next time the same question comes up. Better still, rebuild the example once in your own sheet — doing it yourself, with the tool above to check against, is what turns a copied formula into a technique you own. If you take one thing from this page on “excel vba delete row”, make it the habit rather than the keystrokes: set the problem up with labelled inputs, reference those cells, and let Excel do the recomputing. Bookmark the page for the syntax, but do the example once in a blank sheet and check it against the tool above — five minutes of hands-on practice fixes the method in memory far better than re-reading, and it surfaces the small snags while they are still harmless. After that the technique is genuinely yours: faster than searching for it again, and reliable enough to drop into work that other people depend on.
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
Why does my macro skip rows when deleting?
It is looping forwards. Each deletion pulls the following rows up by one while the loop counter still increments, so the row that moved into the deleted position is never examined. Loop backwards with Step -1.
How do I insert several rows at once?
Reference a range of rows: ws.Rows("10:12").Insert Shift:=xlDown adds three rows above row 10 in a single call, which is faster and shifts the sheet only once.
Does the inserted row keep the formatting around it?
Yes — CopyOrigin controls which side it copies from. The default xlFormatFromLeftOrAbove inherits from the row above; pass xlFormatFromRightOrBelow to take it from the row below instead.
Can I undo rows a macro deleted?
No. VBA actions bypass Excel's undo stack, so Ctrl + Z cannot recover them. Run destructive macros on a copy, or save the workbook immediately before running one.
Other ways people ask this
People reach this page typing “delete rows in excel using vba”, “excel vba to delete rows”, “delete row excel vba” and “excel vba delete rows”, among other phrasings; whichever wording you used, the fix above is the one you want.
Why do people search for this in so many different ways?
Because the same task has many names. “delete rows in excel using vba”, “excel vba to delete rows”, “delete row excel vba” all point at the one operation explained on this page, which is why they all lead here.