How to Delete Blank Rows in Excel with a VBA Macro

To delete blank rows in Excel with a macro, select the data range, run a VBA procedure that checks each selected row, and confirm the deletion. The macro below removes an entire worksheet row only when every cell in that row’s selected columns is empty. Save a separate copy first: deleting rows with VBA cannot be undone with Ctrl+Z.

What You’ll Need

Excel desktop for Windows or Mac. Excel for the web can open a macro-enabled workbook, but it cannot run or edit VBA macros.
A workbook containing the data you want to clean. Save a backup copy before running the macro.
A selection containing the data columns and rows to check. Leave the header out, and do not select entire columns unless you intend to process a very large range.
Permission to use VBA macros. If your organization blocks macros, ask your administrator rather than changing security settings to allow all macros.

Step-by-Step

Prepare and review the worksheet. Save the workbook, then make a separate copy using File > Save As or your usual backup method. Look over the worksheet and identify the exact columns that define a blank row. For example, if a table has columns A through D, select those columns for each data row. A row with a note in column F will still be deleted if F is outside the selected columns, because this macro judges blankness only within the selected range. Since it deletes whole worksheet rows, check whether unrelated information occupies the same rows elsewhere on the sheet.
Select the data block to check. Drag across the rectangular range that includes all relevant data columns and rows, but not the header. For example, if the headings are in row 1 and the records run from rows 2 to 100, select the data block in rows 2 through 100. Include every column that should protect a row from deletion. Do not select scattered, nonadjacent cells; the macro is designed for one continuous range.
Open the Visual Basic Editor. In Windows Excel, press Alt+F11. If that shortcut is unavailable, use Developer > Visual Basic. On Mac, use Developer > Visual Basic; depending on the keyboard, Option+F11 or Fn+Option+F11 may also open it. If the Developer tab is missing in Windows, go to File > Options > Customize Ribbon, select Developer, and choose OK. On Mac, enable Developer in Excel > Settings > Ribbon & Toolbar.
Add a standard module. In the Visual Basic Editor, choose Insert > Module. A blank code pane appears. A standard module is the right place for a macro that you run on a selected range; do not paste this code into a worksheet’s code window or the ThisWorkbook window.

Paste the macro into the module. Copy the complete code below. It checks the selected columns from the bottom row upward, which prevents a deletion from making the next row get skipped. It also asks for confirmation and reports how many rows it removed.

Option Explicit

Sub DeleteBlankRowsInSelection()
    Dim target As Range
    Dim rowCheck As Range
    Dim ws As Worksheet
    Dim firstRow As Long
    Dim lastRow As Long
    Dim firstCol As Long
    Dim lastCol As Long
    Dim r As Long
    Dim deletedRows As Long

    ' Require one continuous cell range on a worksheet.
    If TypeName(Selection) <> "Range" Then
        MsgBox "Select one continuous cell range first.", vbExclamation
        Exit Sub
    End If

    Set target = Selection
    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
    firstCol = target.Column
    lastCol = target.Column + target.Columns.Count - 1

    If MsgBox("Delete entire worksheet rows that are blank within the selected columns?", _
        vbYesNo + vbExclamation + vbDefaultButton2, "Confirm row deletion") <> vbYes Then
        Exit Sub
    End If

    ' Work upward so deleting a row does not skip the next one.
    For r = lastRow To firstRow Step -1
        Set rowCheck = ws.Range(ws.Cells(r, firstCol), ws.Cells(r, lastCol))
        If Application.CountA(rowCheck) = 0 Then
            ws.Rows(r).Delete
            deletedRows = deletedRows + 1
        End If
    Next r

    MsgBox deletedRows & " blank row(s) deleted.", vbInformation
End Sub

Save as a macro-enabled workbook. Return to Excel and save the file as an Excel Macro-Enabled Workbook (*.xlsm), using File > Save As and choosing that file type. If you save as .xlsx, Excel does not keep the VBA project in the workbook. Keep the backup copy separate from the file you plan to clean.
Run the macro on your selection. Make sure the intended range is still selected. In Windows, you can return to the editor and press F5 while the cursor is inside the procedure. Alternatively, in Excel choose Developer > Macros, select DeleteBlankRowsInSelection, then choose Run. On Mac, use Developer > Macros if available, or run the procedure from the Visual Basic Editor. Read the confirmation prompt before choosing Yes.
Check the cleaned result. Review the surrounding rows, formulas, totals, and any other data on the worksheet. The completion message gives the number of rows deleted. Save the workbook again after confirming the result. If anything important disappeared, close without saving and reopen the backup; Ctrl+Z does not reverse a VBA row deletion.

Common Mistakes

Selecting too few columns: If the range covers only columns A to C, a row containing information in column D can be treated as blank. Include every column whose contents should keep a record. Conversely, selecting columns that contain unrelated notes can cause apparent blanks to be retained.

Including the header: A blank heading cell does not necessarily mean the header row should be deleted. Start the selection at the first data row unless you intentionally want the macro to consider the heading. Similarly, do not select extra rows above or below the records, because blank rows inside the selection qualify for deletion.

Thinking a formula that displays nothing is an empty cell: The macro uses CountA. A formula such as one that returns an empty string still counts as content, even when the cell looks blank. Cells containing spaces also count as content. That behavior helps avoid deleting rows with formulas or invisible text, but it may leave some rows that look empty on screen.

Forgetting that whole worksheet rows are deleted: The selected columns determine the blank test; they do not limit the deletion to those columns. When a row qualifies, the entire row is removed, including cells outside the selected range. Check for side-by-side lists, notes, or calculations before running the macro.

Expecting Ctrl+Z to restore the sheet: Excel clears the normal undo history for many VBA operations. A backup is the reliable recovery option. Do not test a deletion macro on the only copy of important data.

Tips

For a small table, select the range deliberately rather than relying on an automatic selection that might include extra columns. If you are uncertain which columns contain meaningful values, inspect the right edge of the data and any notes or helper fields before deciding. A quick test on a copied worksheet can confirm what the macro considers blank.

Remember that formatting by itself does not make a cell count as populated. A cell with a fill color or border but no value or formula can still be part of a row the macro deletes. Hidden rows within the selected range are checked too; hiding a row does not protect it. If hidden rows must remain, remove them from the planned selection or adapt the code deliberately before use.

For repeat work, keep the macro in a trusted .xlsm workbook or in a personal macro workbook only if you understand how that affects availability across files. Give the procedure a clear name and keep a short note describing which columns it checks. Avoid broad changes to macro security: enable a macro only when you recognize and trust the workbook and its source.

Troubleshooting

The macro is not listed: Confirm that the code is in a standard module, that the procedure name is DeleteBlankRowsInSelection, and that the workbook was saved as .xlsm. Close and reopen the macro list if needed. Excel for the web cannot run the VBA procedure, so open the workbook in desktop Excel.

Excel displays a security warning or blocks the macro: Check that you opened the correct workbook and that it came from a source you trust. Use the workbook’s permitted Enable Content option only when appropriate. A workplace policy may prevent macros from running; contact the administrator instead of enabling all macros or bypassing that policy.

No rows are deleted even though some look empty: Check the selected columns for formulas returning empty text, spaces, apostrophes, or other values. CountA treats formulas and spaces as content. To identify the cells, inspect the formula bar or use Find to look through the selected area. Decide whether those values should be removed or whether the blank test needs a carefully designed change.

The wrong rows are removed: Stop and do not save further changes. Because the macro cannot be undone, close the workbook without saving and reopen your backup. Before another run, confirm the selected range and inspect data in other columns on the same worksheet rows. The macro deletes full rows, not just cells in the selection.

The code reports an error around a merged cell or protected sheet: Merged cells can complicate row checks and deletion, while worksheet protection can prevent row deletion. Make a backup, unmerge relevant cells if that is appropriate for the layout, or unprotect the sheet with authorization before trying again. Do not remove protection if you do not have permission.

Frequently Asked Questions

Does this macro remove rows that are partly blank?
Only when every cell in the selected columns for that row is empty. A value outside the selection does not affect the test, although the entire worksheet row is deleted if it qualifies.

Will it delete rows with formulas that show a blank result?
No. CountA counts a formula as content even if its displayed result looks empty. A cell containing spaces is also counted, so these rows remain unless you clean those entries or change the test.

Can I run the macro on an Excel table?
You can select a table’s data range, but this macro deletes entire worksheet rows, not just table cells. Check for other content on those rows and test on a copy first. For a table-specific cleanup, use Excel’s table row deletion controls or a macro designed specifically for that table.

Can I undo the macro with Ctrl+Z?
No. VBA actions generally cannot be undone through Excel’s normal undo command. Close without saving and restore the backup if the result is wrong.

Will this work in Excel for the web?
No. Excel for the web does not run VBA macros. Use Excel desktop for Windows or Mac to add and run the procedure.

Can I make it check only certain columns but delete only cells in those columns?
Not with this code: it uses the selected columns to decide whether a row is blank, then deletes the entire worksheet row. That is intentional for removing blank records from a list. If you need to clear selected cells while preserving other cells on the same rows, use a different approach and test it on a 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.