Delete Blank Rows in Excel VBA Safely and Correctly

To delete completely blank rows with VBA in Excel for Microsoft 365 on Windows, run a macro that checks each row from the bottom upward and deletes rows with no values or formulas. The example below checks the used columns on the active worksheet, keeps row 1 as a header, and deletes only rows that are empty across that full column span.

What counts as a blank row?

A row that looks empty is not always empty to Excel. It may contain a formula that displays an empty string, a space character, or data in a column far to the right of the visible table. Formatting by itself, such as a fill color or border, generally does not make a cell count as containing data. These differences matter because a macro that checks only a key column or a small range can delete rows that still hold information elsewhere.

The macro below defines a blank row as a row with no constants or formulas between column A and the sheet’s last used column. A formula such as =IF(A2="","",A2) still counts as content even if its displayed result is blank. That is a cautious choice: it avoids removing rows that contain formulas someone may want to keep. If you instead want to treat formulas returning empty text as blank, that requires a different test and should be used only after reviewing what the formulas do.

VBA macro to remove completely blank rows

Save a copy of the workbook before running this macro. Deleting rows with VBA cannot be reversed with Ctrl+Z, so a backup is the simplest way to recover if the result is not what you intended. The macro works on the active worksheet, searches for the last cell containing a value or formula, and checks rows 2 through that last row. It deliberately leaves row 1 alone as a presumed header.

Sub DeleteCompletelyBlankRows()
    Dim ws As Worksheet
    Dim lastRowCell As Range
    Dim lastColCell As Range
    Dim lastRow As Long
    Dim lastCol As Long
    Dim rowNum As Long

    Set ws = ActiveSheet

    ' Find the last cell containing a value or formula.
    Set lastRowCell = ws.Cells.Find(What:="*", After:=ws.Cells(1, 1), _
        LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False)

    If lastRowCell Is Nothing Then
        MsgBox "The worksheet is empty.", vbInformation
        Exit Sub
    End If

    ' Find the last used column so the check covers the full data width.
    Set lastColCell = ws.Cells.Find(What:="*", After:=ws.Cells(1, 1), _
        LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, _
        SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False)

    lastRow = lastRowCell.Row
    lastCol = lastColCell.Column

    ' Work upward so deleting a row does not skip the next row to check.
    For rowNum = lastRow To 2 Step -1
        If Application.CountA(ws.Range(ws.Cells(rowNum, 1), _
            ws.Cells(rowNum, lastCol))) = 0 Then
            ws.Rows(rowNum).Delete
        End If
    Next rowNum

    MsgBox "Completely blank rows have been deleted.", vbInformation
End Sub

Because the test spans from column A to the last used column anywhere on the sheet, a value in a distant column prevents that row from being deleted. If your sheet has unrelated notes or another dataset far to the right, this behavior may preserve rows you expected to remove. In that situation, narrow the check to the actual dataset only after confirming that deleting entire worksheet rows will not remove other information.

How to add and run the macro

Open the workbook in the desktop version of Excel. Excel for the web can store a macro-enabled workbook, but it cannot create or run VBA macros.
Save a backup copy. To retain VBA in the working file, use Excel Macro-Enabled Workbook (*.xlsm) when saving.
On Windows, open the Visual Basic Editor with Alt+F11. You can also use Developer > Visual Basic. If the Developer tab is missing, enable it through File > Options > Customize Ribbon.
In the editor, choose Insert > Module, paste the code into the module, and return to Excel.
Select the worksheet you intend to clean, then run the macro from Developer > Macros. Select DeleteCompletelyBlankRows and choose Run. In the Visual Basic Editor, F5 can also run the procedure when the cursor is inside it.

On a Mac, use the Developer tab to open Visual Basic; depending on the keyboard and function-key settings, the editor shortcut may require Option+F11 or Fn+Option+F11. Excel for Mac supports VBA, but keyboard behavior and some Excel features differ from Windows. If you do not see Developer, enable it in Excel’s ribbon settings. Review the selected sheet before running the macro, because the code uses ActiveSheet rather than a sheet name.

Why the loop runs from bottom to top

When a macro deletes a row, every row beneath it shifts up by one. A loop that starts at row 2 and moves downward can therefore skip a row: after row 2 is deleted, the original row 3 moves into row 2, while the loop advances to row 3. Iterating backward avoids that problem. Deleting row 20 does not change the position of rows 1 through 19, so each remaining candidate is checked once.

The search uses Find with formulas included to locate the last content-bearing row and column. This is more precise than relying only on UsedRange, which can extend farther than visible data after cells have been formatted or edited. The macro’s CountA check counts formulas as occupied cells, even when their displayed results look empty. It does not regard formatting alone as content.

Change the macro for a specific dataset

Consider a table with headers in row 1 and records in columns A through F. The supplied macro checks every column from A to the last used column on the sheet, which is the safer default if the sheet contains one dataset. If the data is definitely limited to A:F, you can restrict the row test to those columns and calculate the final row from that same dataset. Do not make that change when other important content may sit elsewhere on the row: the command ws.Rows(rowNum).Delete removes the entire worksheet row, not merely cells A:F.

To change which rows are protected, edit the loop’s starting number. For example, changing For rowNum = lastRow To 2 Step -1 to start at 1 allows the macro to delete a blank first row too. That is usually inappropriate when row 1 contains field names. If a title occupies rows 1 and 2 and the table headers are in row 3, start at row 4 instead. The rule is to begin checking immediately below the last row that must be retained.

For a very large worksheet, deleting rows one at a time can take time because Excel updates the sheet after each deletion. For a few gaps this is straightforward and easy to inspect. If a sheet has hundreds or thousands of blank rows, filtering or rebuilding the data in a new range may be faster, but those approaches need careful checks to preserve formulas, formatting, and row order.

Common problems and safety checks

The wrong sheet changes: the macro targets ActiveSheet. Click the intended tab first, or change Set ws = ActiveSheet to a specific worksheet reference after confirming its exact name.
Rows with some data are removed: this can happen with a modified macro that checks only a key column. The supplied macro checks across the sheet’s used width, but deleting rows still affects every column in those rows.
Rows that look blank remain: they may contain formulas returning an empty string, spaces, or values outside the part of the sheet you inspected. Use Excel’s formula bar or Go To Special to investigate before broadening the deletion rule.
Filtered or hidden rows are affected: the macro does not limit its work to visible rows. It checks rows in the worksheet range, including hidden rows. Remove or account for filters and hidden data before running it if that distinction matters.
Undo does not restore the rows: VBA actions clear Excel’s normal undo history. Close without saving if you have not made other changes and want to discard the macro’s result, or restore the backup copy.

Macros can contain harmful code, so run VBA only in workbooks and modules you trust. Excel may show a security warning when opening an .xlsm file; do not enable macros just to inspect an unfamiliar workbook. For a one-time cleanup, a manual method may be preferable to changing macro security settings.

Manual alternatives and when to use them

For a short list, inspect the sheet and delete rows individually: select the row numbers, right-click, and choose Delete. This is slower than a macro but makes it easy to verify each row. For a larger table with a reliable required field, apply a filter to that field, show blank entries, select the visible row numbers, and delete the entire rows. This method is appropriate only if a blank in that field truly means the whole record is empty; otherwise, it could remove rows with useful data in other fields.

Go To Special can select blank cells, but selecting blank cells is not the same as identifying completely blank rows. If you select blanks in a table and choose Delete Sheet Rows, Excel may remove rows that contain other values in different columns. Use that technique only when the selected blanks accurately represent rows that can be removed, and inspect the selection before confirming. For a structured Excel table, also check whether blank records are actually inside the table or merely below it; empty worksheet space beneath a table does not need to be deleted.

Frequently Asked Questions

Does the macro delete rows that contain formulas showing nothing?

No. The macro counts formulas as content, even if a formula displays an empty string. That conservative behavior protects formulas that may be needed later. Removing those rows requires a different test and should be based on the formula and data requirements, not appearance alone.

Can I delete blank rows in only columns A through F?

Yes, but first confirm that the rows contain no important information in other columns. Change the range used by the blank-row test to A:F and keep in mind that ws.Rows(rowNum).Delete still deletes the entire worksheet row, including cells outside A:F.

Will this macro work in Excel for the web?

No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to run the procedure. If you need a browser-based automation option, investigate Office Scripts, which is a separate tool with a different language and feature set.

Can I undo a VBA row deletion with Ctrl+Z?

No. Running VBA clears Excel’s regular undo history, so Ctrl+Z will not restore rows deleted by the macro. Save a separate copy before running it; if the result is wrong, restore that copy or close without saving if appropriate.

Why does the macro leave some apparently blank rows?

A cell in the row may contain a formula, a space, or another value that is not obvious in the worksheet view. The macro also checks the full used width, so content far to the right can keep a row from being deleted. Inspect the row before changing the criteria.

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.