Delete Rows From an Input Range in VBA

To delete worksheet rows covered by a range you select in Excel VBA, use Application.InputBox with Type:=8, then delete the corresponding rows with EntireRow.Delete or the worksheet’s Rows collection. The example below accepts one rectangular range, confirms the action, and removes all rows that intersect it.

Select a range and delete its rows

In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste in this macro. Run it from the editor with F5, or run it from Excel using Developer > Macros. On a Mac, Option+F11 (sometimes Fn+Option+F11) opens the editor; the Developer tab’s Macros command can also run the procedure. Save a separate copy of the workbook before running it: deleting rows with VBA cannot be undone with Ctrl+Z.

Sub DeleteRowsFromInputRange()
    Dim picked As Variant
    Dim target As Range
    Dim ws As Worksheet
    Dim firstRow As Long
    Dim lastRow As Long

    ' Ask the user to select cells in the worksheet.
    picked = Application.InputBox( _
        Prompt:="Select the range whose rows you want to delete.", _
        Title:="Choose input range", Type:=8)

    ' Cancel returns False; leave the workbook unchanged.
    If VarType(picked) = vbBoolean Then
        If picked = False Then Exit Sub
    End If

    Set target = picked

    ' This example deliberately handles one continuous rectangle.
    If target.Areas.Count <> 1 Then
        MsgBox "Select one continuous range, not separate areas.", vbExclamation
        Exit Sub
    End If

    Set ws = target.Worksheet
    firstRow = target.Row
    lastRow = target.Row + target.Rows.Count - 1

    ' Guard against accidentally selecting an entire worksheet column.
    If target.Rows.Count = ws.Rows.Count Then
        MsgBox "Select only the rows you intend to remove.", vbExclamation
        Exit Sub
    End If

    If MsgBox("Delete worksheet rows " & firstRow & " through " & _
              lastRow & " on " & ws.Name & "?", _
              vbYesNo + vbQuestion, "Confirm deletion") <> vbYes Then Exit Sub

    ' Delete the selected row span in one operation.
    ws.Rows(firstRow & ":" & lastRow).Delete
End Sub

The selected cells determine which row numbers are removed, not which individual cells are cleared. For example, selecting B4:D7 deletes worksheet rows 4, 5, 6, and 7 across every column. It does not just erase the contents in columns B through D. If the goal is to clear values while keeping the rows and their formatting, use ClearContents on the chosen range instead; that is a different operation.

What the input range means

The argument Type:=8 tells Application.InputBox to return a Range object. The user can select cells with the mouse or enter an address such as A2:C10. Storing the result in a Variant first makes it possible to distinguish Cancel, which returns the Boolean value False, from a range selection. The Set statement then assigns that returned object to the Range variable.

Because a selection can be on a different sheet from the one that was active before the prompt, the macro gets the worksheet from target.Worksheet. It does not assume that the active sheet is the correct destination. This matters when a workbook has several sheets: the row numbers in the selection refer to the sheet where the selection was made, and that is the sheet the example deletes from.

The macro accepts a rectangular, continuous selection. A selection such as A2:A5 or B2:D5 qualifies; a Ctrl-clicked collection of separate ranges does not. The code checks Areas.Count and exits with a message rather than risking confusing results. For predictable behavior, select a compact range of cells that makes the intended row span obvious.

Steps to run the macro safely

Save a copy of the workbook under a different name. VBA row deletions do not populate Excel’s undo history.
Open the Visual Basic Editor, insert a standard module, and paste the code. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if you need to retain the macro.
Run DeleteRowsFromInputRange. When Excel prompts you, select cells on the sheet whose rows should be removed, then confirm the prompt.
Inspect the affected sheet and any dependent formulas, tables, charts, or references. If the selection was wrong, close without saving and reopen the saved copy, or restore the backup.

Excel may require macros to be enabled for a workbook you trust. Do not lower security settings or enable macros in an unexpected file just to run this procedure. On Windows, macros can be run from Developer > Macros after the Developer tab is enabled in Excel Options. On Mac, use the Macros command on the Developer tab or the Visual Basic Editor. The VBA code itself is similar on both platforms, although keyboard shortcuts and some platform-specific automation features can differ.

Why row deletion order matters

The main example deletes one contiguous block with a single statement, so shifting rows do not change the remaining row numbers during a loop. A different problem arises when code tests rows one at a time and deletes those that meet a condition. If it scans from the top downward, deleting row 5 shifts the old row 6 into row 5; advancing the loop to row 6 then skips that shifted record.

For conditional deletion, scan from the last used row upward. Deleting a lower row does not change the row numbers of records above it. For instance, a loop that starts with the last data row and uses Step -1 can safely inspect each row and delete matches. This bottom-up technique is important when deleting several separate rows; it is not required for a single continuous block deleted in one operation.

For very large conditional deletions, deleting one row at a time can also be slow because Excel repeatedly updates the sheet. A faster design may first identify rows to keep, filter the data, or build a compact replacement range. Choose that approach only when it preserves the workbook’s formulas, formatting, tables, and other structures as intended.

If the input range contains criteria values

Sometimes “based upon input range” means something different: the range contains IDs or other values, and the macro should delete data rows whose key appears in that list. Selecting a block and deleting its rows is not the same as matching values. The following example assumes IDs are in column A of a sheet named Data, the criteria list is in A2:A100 on a sheet named Criteria, and row 1 on Data contains headers. Change those sheet names, columns, and boundaries to match the workbook.

Save a copy before running this alternative as well. It uses an exact-match lookup for each data ID and scans upward from the bottom. Blank criteria cells are ignored so an empty key in the data is not treated as a match for a blank list entry.

Sub DeleteRowsMatchingCriteriaList()
    Dim wsData As Worksheet
    Dim wsList As Worksheet
    Dim ids As Range
    Dim lastRow As Long
    Dim r As Long
    Dim matchResult As Variant
    Dim keyValue As Variant

    ' Set these names and ranges to match the workbook layout.
    Set wsData = ThisWorkbook.Worksheets("Data")
    Set wsList = ThisWorkbook.Worksheets("Criteria")
    Set ids = wsList.Range("A2:A100")

    lastRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row

    ' Work upward so deleting a match does not skip the next record.
    For r = lastRow To 2 Step -1
        keyValue = wsData.Cells(r, "A").Value
        If Len(CStr(keyValue)) > 0 Then
            matchResult = Application.Match(keyValue, ids, 0)
            If Not IsError(matchResult) Then
                wsData.Rows(r).Delete
            End If
        End If
    Next r
End Sub

Application.Match with a final argument of zero looks for an exact match. Matching depends on how Excel stores the IDs: a numeric value and a text value that looks like a number may not behave as the same key in every situation. If IDs can have leading zeros, store and compare them consistently as text. Also review the criteria range for duplicates or unexpected spaces; duplicates do not cause multiple deletions, but inconsistent values can mean a row is not matched.

Important effects and common problems

Entire rows are affected: deleting row 4 removes its contents across the worksheet, not just in the selected columns. Verify the row span before confirming.
Formulas and references can change: Excel adjusts many formulas when rows are removed, but external links, text-based references, macros, and complex models may need separate review.
Tables and filters need care: deleting worksheet rows that intersect an Excel table can change the table’s data. If the intention is to remove table records, deleting the table rows through its data range or filtering and deleting visible records may be clearer.
Protected sheets can block deletion: the macro may stop with an error if the worksheet is protected and row deletion is not permitted. Unprotect it only when authorized, then restore protection as appropriate.
Filtered data can be misleading: a worksheet selection may include hidden rows. The first macro deletes the full row span whether rows are visible or hidden; it does not mean “delete visible rows only.”
Large selections may be accidental: selecting a whole column covers more than a million rows in current Excel worksheets. The example rejects a full-column selection, but always check the address shown in the prompt.

For visible rows only, use a procedure designed to identify visible cells and check the resulting range before deletion; do not assume EntireRow.Delete automatically limits itself to visible records. Similarly, merged cells and workbook-specific structures can impose restrictions. Test specialized deletion logic on a copy, especially when the sheet contains grouped rows, subtotals, or formulas tied to fixed positions.

Choosing between deletion and clearing

Use row deletion when the records should disappear and the remaining rows should move up. Use ClearContents when the row positions must stay fixed but cell values should be removed. Clearing leaves formatting and the row itself in place; deleting shifts other cells upward and can change the layout. If the intent is to remove only selected cells and shift neighboring cells, that is another operation and should be expressed with a specific range-deletion direction rather than deleting entire worksheet rows.

For repeatable tasks, a fixed range in the code can be more appropriate than prompting each time. For one-off cleanup, the range prompt is convenient because the user chooses the target at runtime. For rules based on values, write the rule explicitly—such as matching IDs, deleting blanks, or removing dates before a cutoff—rather than relying on a range selection that could be mistaken for a criteria list.

Frequently Asked Questions

How do I delete a row selected by the user in VBA?
Use Application.InputBox with Type:=8 to collect a Range object, then delete the row or rows that overlap it. Check for Cancel and confirm the worksheet and row numbers before removing data.

Does selecting A2:C5 delete only those cells?
No. In the example, that selection identifies rows 2 through 5, and the macro deletes those entire worksheet rows across all columns. Use ClearContents if you want to clear only the selected cells without shifting rows.

Can I select non-contiguous ranges?
The first macro intentionally rejects a selection with multiple areas. Supporting separate areas requires handling each affected row only once and deleting rows in descending order, so row shifts do not change the remaining targets.

How do I delete rows when a value appears in another range?
Use the criteria-list approach: check each data key against the supplied list with an exact match, and scan data rows from bottom to top. Make sure the compared values use consistent types and that blank criteria do not create unwanted matches.

Can I undo a VBA row deletion with Ctrl+Z?
No. Excel clears its normal undo history when VBA changes the workbook. Save a backup before running a deletion macro; if you need to reverse the change, restore the backup or use a recovery copy.

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.