VBA Delete Row and Shift Up: Excel Macro Examples

To delete an entire worksheet row with VBA and move the rows below it up, use Rows(rowNumber).Delete, such as ws.Rows(5).Delete. To delete only a group of cells and shift the remaining cells up within that range, use Range("B5:D5").Delete Shift:=xlUp; these commands affect different amounts of the worksheet.

What “delete row and shift up” means in Excel

Excel’s delete operation removes cells and closes the resulting gap. When you delete a whole worksheet row, every cell in that row is removed, and the rows underneath move up one row. For example, deleting row 5 moves the former row 6 into row 5, the former row 7 into row 6, and so on. The worksheet still has the same number of available rows; the cells are rearranged rather than the worksheet becoming shorter.

“Shift up” can also refer to deleting only part of a row. If you delete B5:D5 and choose to shift cells up, Excel moves cells upward within columns B through D. The cells in columns A and E are not shifted. That can be useful for a small, independent block, but it can misalign records if the columns belong to the same table.

Before writing a macro, decide whether the unwanted data is a complete record spanning the worksheet or just a set of cells in a local range. For a normal data list, deleting the entire row is usually the appropriate choice because it keeps the other fields in each record together.

VBA code to delete a whole worksheet row

This example deletes row 5 on the active worksheet. It uses a worksheet variable so the target is explicit in the deletion statement. Change the row number to the row you intend to remove.

Sub DeleteWholeRow()
    Dim ws As Worksheet
    Dim rowToDelete As Long

    ' Use the active worksheet; confirm it is the intended sheet.
    Set ws = ActiveSheet
    rowToDelete = 5

    ' Delete the entire worksheet row and shift rows below it up.
    ws.Rows(rowToDelete).Delete
End Sub

Save a copy of the workbook before running a macro: VBA deletions cannot be reversed with Ctrl+Z. On Windows, open the Visual Basic Editor with Alt+F11, insert a standard module using Insert > Module, paste the procedure, and run it with F5 while the cursor is inside the procedure. You can also run a saved macro from Developer > Macros. On Mac, use Developer > Visual Basic or Option+F11 to open the editor; some keyboards require Fn as well. The Developer tab may need to be enabled in Excel’s ribbon settings.

The code uses ActiveSheet, so the active tab matters. If a different sheet should always be targeted, set ws to that worksheet explicitly instead of relying on whichever sheet happens to be selected. For example, a workbook can use a worksheet reference by name; verify the sheet name in the code against the tab name before running it.

Delete cells in a range and shift only those cells

To remove cells from a limited area rather than remove a complete row, specify a range and set the direction explicitly. The following example deletes B5:D5 and shifts the cells below that range upward in those columns only:

Sub DeleteCellsAndShiftUp()
    Dim ws As Worksheet

    ' Use the active worksheet; check the selected sheet first.
    Set ws = ActiveSheet

    ' Delete these cells and move cells below them up within columns B:D.
    ws.Range("B5:D5").Delete Shift:=xlUp
End Sub

The Shift:=xlUp argument makes the intended direction clear. Without a direction, Excel may need to infer how the remaining cells should move, especially when the deleted range is not a complete row or column. A rectangular selection can also be deleted with Shift:=xlToLeft when the desired result is to move cells from the right into the gap, but that is a different operation.

Use range deletion only when the columns are independent or when shifting just those cells is genuinely intended. In a table containing a person’s name, date, and status across several columns, shifting only one or two columns upward can attach values to the wrong person. If an entire record is obsolete, delete its worksheet row or remove the corresponding table row instead.

Delete rows that meet a condition

A common task is removing every record whose status in column A is “Closed,” while preserving the header in row 1. When deleting multiple worksheet rows in a loop, count from the bottom toward the top. If the macro started at the top, each deletion would shift later rows upward and change their row numbers; the loop could skip a record. Working upward from the last used row avoids that problem.

Sub DeleteClosedRows()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long

    ' Use the active worksheet and preserve row 1 as a header.
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Work upward so deletions do not change rows still to be checked.
    For i = lastRow To 2 Step -1
        If ws.Cells(i, "A").Value = "Closed" Then
            ws.Rows(i).Delete
        End If
    Next i
End Sub

This assumes the status is in column A, data starts in row 2, and column A is populated far enough down to identify the final record. If column A can be blank on valid records, find the last row using a column that is reliably filled, or determine the last used row across the relevant data area. The comparison is for the exact text “Closed”; if the workbook contains extra spaces, different spelling, or other status labels, adapt the condition to the data rather than assuming every value matches.

For more complicated criteria, test the condition on a few known rows first. Consider writing a list of row numbers to a separate area or counting matching records before deleting anything. That gives you a chance to confirm the macro’s selection logic before it makes a destructive change.

Choosing between a row, a range, and a table row

Pick the deletion method according to the structure of the data, not just the visible selection:

Whole worksheet row: Use ws.Rows(n).Delete when the entire record across the sheet should be removed and the rows beneath should close the gap.
Cells in a specific range: Use Range(...).Delete Shift:=xlUp when only those cells should move, and shifting the other columns would be incorrect.
Excel table record: If the data is an Excel table, deleting the corresponding table row is often clearer because the operation is expressed as removing a record from the table, rather than manipulating a worksheet row by number.

Worksheet rows and table rows are related but not identical concepts. A table can occupy only part of a worksheet, with notes or other data outside it. Removing a worksheet row affects the full sheet row, including cells outside the table; removing a table record targets the table’s data. Check for neighboring content before choosing the broader worksheet operation.

Also distinguish deletion from clearing. ClearContents removes values and formulas but leaves the cells in place, so the rows below do not move up. That is useful when you want to keep the layout, formatting, or row positions. It does not satisfy a request to close the gap.

Common problems and how to avoid them

The most frequent error is deleting a different row from the one intended. Row numbers are not the same as record numbers when a list has a title, header, blank rows, or filtered data. Confirm what the row number refers to, and consider checking a key value in the target row before deletion. A macro that deletes row 5 always deletes worksheet row 5; it does not automatically mean “the fifth visible record.”

Filters and hidden rows need particular care. A loop over row numbers can include records that are hidden or filtered out, because hidden status does not necessarily remove those rows from the worksheet. If the requirement is to delete only visible filtered records, build and verify the visible-cell selection deliberately rather than assuming a simple loop over all row numbers will respect the filter.

Other details that can change the result include merged cells, formulas, and worksheet protection. Deleting rows that intersect merged areas can produce unexpected layout changes or an error. Formulas that refer to deleted cells may adjust or return errors depending on their references and the surrounding formula logic. A protected sheet may prevent deletion unless the sheet is unprotected or the workbook has been configured to allow that action. Review formulas and layout after a test run.

Finally, VBA code may be blocked by macro security settings, or the workbook may need to be saved in a macro-enabled format such as .xlsm to retain its VBA project. Only enable macros in files you trust. These are separate from the deletion syntax: a correctly written procedure still cannot run if Excel is not permitted to execute it.

Manual deletion, undo, and safer alternatives

For a one-time removal, the ribbon may be safer than writing a macro. Select a cell in the row, then choose Home > Cells > Delete > Delete Sheet Rows. You can also select the whole row by clicking its row heading and use the delete command. On Windows, Shift+Space selects the current row and Ctrl+- opens the delete action; choose the whole-row option if Excel asks. On Mac, Shift+Space selects the row, and Control+- is commonly used for deletion, though keyboard behavior can vary with keyboard settings. The ribbon command is a dependable alternative.

Manual deletion is also easier to undo immediately with Ctrl+Z on Windows or Command+Z on Mac. A macro that changes the worksheet generally clears Excel’s normal undo history, so Ctrl+Z will not restore the deleted row after the macro runs. Do not treat a backup as optional when a macro is deleting data. For valuable or shared workbooks, test on a duplicate and keep a recoverable copy before applying the procedure to the original.

Another alternative is to filter the data, inspect the matching records, and then remove them using the appropriate row or table command. For repeatable processes, a macro can save time, but add safeguards such as a confirmation prompt, a count of rows to be deleted, or a separate log of the records being removed. Those measures do not replace a backup, but they make it easier to catch an incorrect criterion before the deletion happens.

Frequently Asked Questions

What is the VBA command to delete a row and shift rows up?
Use Rows(rowNumber).Delete, such as ws.Rows(5).Delete. It deletes the entire worksheet row, so the rows below move up.

How do I shift cells up without deleting the whole row?
Use a specific range with Range("B5:D5").Delete Shift:=xlUp. Only the specified columns shift, so make sure that will not misalign related data.

Why should a VBA loop delete rows from bottom to top?
Deleting a row shifts the rows below it and changes their row numbers. A bottom-up loop avoids moving unchecked rows into positions the loop has already passed.

Can I undo a VBA row deletion with Ctrl+Z?
Usually not. Running a macro that changes the worksheet clears Excel’s standard undo history, so save a backup or test on a copy before running deletion code.

Does deleting a row clear its contents or remove the row?
Rows(n).Delete removes the row’s cells and shifts cells below upward. Clearing contents leaves the row and its position in place; use that only when you want to preserve the layout.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.