Excel VBA: Delete a Row and Shift Up

To delete a complete worksheet row in Excel VBA and move the rows below it up, use Rows(rowNumber).Delete, such as ws.Rows(5).Delete. To delete only a range of cells and shift those cells upward, use Range(...).Delete Shift:=xlUp. Choose the first approach when the whole record should go; the second can leave columns misaligned if the range is only part of a dataset.

What “delete row and shift up” means

Excel’s delete command removes cells and closes the gap they leave. When you delete an entire worksheet row, every worksheet cell in that row is removed and the rows underneath move up by one. In VBA, deleting a row through Rows(5).Delete performs that whole-row operation; specifying EntireRow makes the intention especially clear. You do not need to add Shift:=xlUp when deleting a complete row.

Deleting a range is different. For example, removing B5:D5 with Shift:=xlUp moves cells below that range upward in columns B through D only. Values in columns A and E do not move. That may be exactly right for a small independent list, but it can break the relationship between fields in a table-like dataset. If each row represents one record, use a whole-row or table-row deletion instead.

Delete an entire worksheet row

Use a worksheet variable to identify where the deletion should happen. This avoids relying on whichever sheet happens to be active when the macro runs. The example below deletes row 5 on the worksheet named Data in the workbook that contains the macro:

Sub DeleteFifthRow()
    Dim ws As Worksheet

    ' Point to the worksheet that contains the row.
    Set ws = ThisWorkbook.Worksheets("Data")
    ws.Rows(5).EntireRow.Delete
End Sub

Rows(5) refers to the fifth worksheet row, not the fifth row of a selected range. EntireRow.Delete states explicitly that the entire worksheet row is to be removed. You can also write ws.Rows(5).Delete; for this purpose the result is the same. After deletion, what was row 6 becomes row 5, and Excel adjusts many formulas and references that point to the affected cells.

Replace Data with the exact sheet tab name, including spaces and punctuation. ThisWorkbook means the workbook where the VBA code is stored. If the macro is stored in Personal Macro Workbook or an add-in but should change another workbook, assign the correct workbook explicitly instead of assuming ThisWorkbook is the target.

Delete cells and shift only those cells upward

For a limited range, specify the range and the shift direction. This example removes cells B5 through D5 and shifts the cells below them up within those columns:

Sub DeleteCellsAndShiftUp()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Data")
    ws.Range("B5:D5").Delete Shift:=xlUp
End Sub

The range being deleted is three cells wide, so Excel closes the gap in columns B, C, and D. It does not delete row 5 as a whole. If column A contains a customer name and B:D contain that customer’s details, shifting only B:D would attach data to the wrong name. Before using this method, check whether the selected columns are genuinely independent of neighboring columns.

Excel can sometimes infer a shift direction when the argument is omitted, based on the shape of the deleted range. Stating Shift:=xlUp is clearer and avoids depending on that inference. For a whole row, use a row deletion rather than relying on a cell-range shift.

Delete rows that meet a condition

When deleting records based on a value, loop from the bottom of the data toward the top. If a loop goes downward and deletes the current row, the next row moves into its place; incrementing the loop can then skip that moved row. A bottom-up loop avoids that problem. This example checks column A from row 2 down and deletes rows where the cell contains the word Delete:

Sub DeleteMarkedRows()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim r As Long

    Set ws = ThisWorkbook.Worksheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    For r = lastRow To 2 Step -1
        If ws.Cells(r, "A").Value = "Delete" Then
            ws.Rows(r).EntireRow.Delete
        End If
    Next r
End Sub

This assumes row 1 contains headings and column A is the field used to find records. Change the column, comparison value, or starting row to match the actual layout. The End(xlUp) approach finds the last non-empty cell in column A; if that column has gaps or is not reliably populated, determine the last row from a more dependable field. For case-sensitive matching, partial text, or values in another column, adjust the condition deliberately rather than deleting based on a broad match.

Choose the right method for a dataset or table

For ordinary worksheet data, deleting the entire worksheet row is generally appropriate when all the cells across that row belong to one record. If the data is an official Excel Table, deleting through the table’s ListRows collection better expresses that you mean a table record, rather than every cell across the worksheet. It also avoids treating unrelated content elsewhere on the same worksheet as part of the table operation.

For example, if a table named Sales has a third data row to remove, this code deletes that table row. The index is relative to the table’s data rows, not the worksheet row number; the header is not counted:

Sub DeleteThirdSalesRecord()
    Dim ws As Worksheet
    Dim lo As ListObject

    Set ws = ThisWorkbook.Worksheets("Data")
    Set lo = ws.ListObjects("Sales")

    If lo.ListRows.Count >= 3 Then
        lo.ListRows(3).Delete
    End If
End Sub

Use the table’s actual name, which you can see when a table cell is selected under Table Design. If the table has no data rows, there is no third row to delete; the count check prevents an invalid index. A worksheet row number and a table row index can differ because the table may start well below row 1.

Save a copy and understand undo

Save a separate copy of the workbook before running a deletion macro, especially if the code deletes many rows or uses criteria. VBA actions generally cannot be reversed with Excel’s Ctrl+Z undo command. If a macro removes the wrong records, closing without saving may help only if no other wanted changes have been made since the last save; a backup is the dependable protection.

For a macro-enabled workbook, save the file in a format that supports macros, such as .xlsm. A workbook saved as .xlsx does not retain VBA code. Also consider running a test on a copied sheet or a small sample first. For condition-based deletion, inspect the matching rows before the macro runs, and confirm the header row and search column are what the code assumes.

Common problems and how to avoid them

The wrong sheet changes: Qualify the range with a worksheet variable, as in ws.Rows(5).Delete, rather than using an unqualified Rows(5).Delete that acts on the active sheet.
Rows are skipped: When deleting several rows in a loop, count downward using Step -1. Deleting while counting upward changes the positions of rows that have not yet been checked.
Fields no longer match: Avoid shifting just a few columns upward when each worksheet row is one complete record. Delete the whole row or the matching table row.
The last-row calculation is wrong: A last-row search based on column A will not include records that have no value in column A. Base the search on a consistently populated column or use a suitable table range.
Filtered or hidden rows cause surprises: A loop over worksheet row numbers can still encounter rows hidden by filtering or manually hidden. Decide whether hidden matching rows should be deleted too; do not assume a filter makes every VBA loop operate only on visible records.
Formulas or formatting change: Deletion alters the worksheet structure. Excel adjusts many references, but review formulas, named ranges, charts, and linked data after a substantial cleanup.

Also be cautious with merged cells: a deletion that intersects a merged area can fail or produce an unexpected layout because the merged area spans multiple cells. Resolve or account for merged ranges before automating row removal. If worksheet protection is enabled, the macro may also be unable to delete rows unless the sheet is unprotected or the protection settings allow the operation.

Run the macro in Excel for Microsoft 365

On Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, paste the macro, and return to Excel. If the Developer tab is not shown, enable it through File > Options > Customize Ribbon and select Developer. To run a macro, press Alt+F8, select its name, and choose Run. The worksheet name and data layout in the code must match the workbook.

On Mac, the Visual Basic Editor can be opened from the Developer tab or with Option+F11; some keyboards require holding Fn as well. Shortcut behavior can vary with macOS and keyboard settings, so use the ribbon command if the shortcut is intercepted. The Windows Delete Cells shortcut Ctrl+- opens the deletion choices; on Mac, keyboard mappings can differ, so use the Home tab’s Delete menu if that shortcut does not work. Excel for the web can open and edit a macro-enabled workbook but cannot run VBA macros; use the desktop Excel app to execute this code.

Frequently Asked Questions

Do I need to use Shift:=xlUp to delete a whole row?
No. Use ws.Rows(rowNumber).Delete or ws.Rows(rowNumber).EntireRow.Delete. Excel removes the whole row and moves the rows below up automatically.

What is the VBA syntax to delete a range and shift cells up?
Use Range("B5:D5").Delete Shift:=xlUp, preferably qualified with a worksheet, such as ws.Range("B5:D5").Delete Shift:=xlUp. This shifts cells in the selected columns, not the entire worksheet row.

Why should a deletion loop run from bottom to top?
When a row is deleted, rows below it move up and row numbers change. Counting upward can jump over a row that moved into the position just deleted. A loop using For r = lastRow To firstRow Step -1 checks each intended row safely.

Can I undo a VBA row deletion with Ctrl+Z?
Usually not. Running a macro clears Excel’s normal undo history, so save a copy before running code that deletes data.

Can VBA delete only visible rows after filtering?
It can, but a basic loop through worksheet row numbers does not automatically mean “visible rows only.” You must deliberately identify visible cells or otherwise account for the filter, and test the result on a copy before deleting records.

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.