VBA Delete Blank Rows in Excel: Safe Methods

Yes. In Excel for Microsoft 365 on Windows, a VBA macro can scan a chosen range from bottom to top and delete rows that are blank across the columns you specify. The macro below checks columns A:F, keeps row 1 as a header, and treats formulas that return an empty string as blank; save a copy first, because deleting rows with VBA cannot be undone with Ctrl+Z.

What counts as a blank row?

“Blank row” can mean different things. A row may be completely empty, empty only in the columns that define your data, or merely look empty because formulas return an empty string. Decide which meaning you need before deleting anything. For example, a row with a customer name in column A and no value in column F is not an empty record if the other cells contain data.

The macro uses columns A:F as its test range. It considers a cell blank when its displayed value is an empty string, so it treats a formula such as =IF(A2="","",A2) as blank when that formula currently returns nothing. It does not treat a cell containing spaces as blank, and it treats an error value such as #N/A as content. These rules help avoid deleting rows that contain a value you may need to investigate.

Crucially, the macro deletes the entire worksheet row if all six tested cells are blank. If column G or another column contains information on that same row, that information will be deleted too. Include every column that belongs to the records you intend to clean, and check whether the sheet has unrelated material outside the data table.

VBA macro to delete blank rows

On a worksheet with headers in row 1 and data in columns A:F, use this macro. It starts at the last row containing something in A:F and works upward, deleting a row only when every tested cell has an empty value.

Sub DeleteBlankRowsInData()
    Dim ws As Worksheet
    Dim lastCell As Range
    Dim rowRange As Range
    Dim cell As Range
    Dim rowNum As Long
    Dim hasContent As Boolean

    Set ws = ActiveSheet

    ' Find the last cell with a value or formula in the data columns.
    Set lastCell = ws.Range("A:F").Find( _
        What:="*", _
        After:=ws.Range("A1"), _
        LookIn:=xlFormulas, _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False)

    If lastCell Is Nothing Then Exit Sub

    ' Work upward so deleting a row does not skip the next one.
    For rowNum = lastCell.Row To 2 Step -1
        Set rowRange = ws.Range("A" & rowNum & ":F" & rowNum)
        hasContent = False

        For Each cell In rowRange.Cells
            If IsError(cell.Value2) Then
                hasContent = True
                Exit For
            ElseIf Len(CStr(cell.Value2)) > 0 Then
                hasContent = True
                Exit For
            End If
        Next cell

        If Not hasContent Then ws.Rows(rowNum).Delete
    Next rowNum
End Sub

The & and > shown in the HTML code block represent VBA’s ordinary ampersand and greater-than characters; when entering the macro in the Visual Basic Editor, use & as & in the actual code editor? No: enter the VBA operators as & and > only if they appear as those characters in the displayed code. In ordinary VBA syntax, the line joins text with an ampersand and the comparison uses the greater-than character.

How the macro works

The search is limited to A:F, so content in those columns determines the last row to inspect. The Find method uses LookIn:=xlFormulas so it can locate cells containing formulas, including formulas whose current result looks empty. It then checks each row’s values. This is different from checking whether cells have formulas: a formula returning an empty string is treated as blank by this macro.

The loop runs from the bottom toward row 2. If a macro deleted row 10 while moving downward, the old row 11 would shift into row 10, and a simple increasing counter could skip it. Moving upward avoids that shifting problem. The loop begins at row 2 to preserve the header; change the starting boundary only if your sheet has a different layout.

When every tested cell is blank, ws.Rows(rowNum).Delete removes the whole worksheet row, not just cells A:F. That keeps the remaining data together, but it is also why checking the range and making a backup matter.

Run the macro safely

Save a separate copy of the workbook before running the code. Row deletions made by a macro cannot be reversed with Ctrl+Z.
Confirm the data is on the active worksheet, headers are in row 1, and all record columns are covered by A:F. Edit both occurrences of the column range in the code if your data uses different columns.
Save the working workbook as an Excel Macro-Enabled Workbook (.xlsm) if you need to keep the macro in the file.
In Excel for Windows, open the Visual Basic Editor with Alt+F11, choose Insert > Module, paste the code, and close or switch back to Excel. Run it with Alt+F8, select DeleteBlankRowsInData, and choose Run.
Review the cleaned worksheet and compare it with your backup, especially if formulas, hidden rows, or data outside the tested columns are involved.

On a Mac, Excel’s Visual Basic Editor is available from Tools > Macro > Visual Basic Editor; the keyboard shortcut can depend on the keyboard’s function-key settings. Excel for the web does not run VBA macros, so open the workbook in the desktop Excel application. Only enable macros in workbooks you trust.

Choose the right columns and header row

Set the test range to the full width of the records, not merely the column where blanks are most noticeable. For a table in B:G with headers in row 3, for instance, change the range used for the search and row test to B:G, and change the loop’s lower limit so it starts at row 4. The last-row search and the row-by-row test must use matching data columns.

There is no universal best range. If a row is considered empty only when every field in a record is empty, check all record fields. If records are defined by a required ID in column A, a blank ID may be a better cleanup criterion than checking every column—but that is a different rule. A row with a missing ID can still contain valuable data, so filtering and reviewing those rows before deletion may be safer.

Be especially careful with notes, formulas, or other content outside the table. If a worksheet has unrelated entries in columns G onward, the macro may delete those entries when it removes a row that is blank in A:F. Move unrelated content to another sheet, expand the logic to account for it, or use a method that does not delete entire worksheet rows.

Alternatives to a VBA macro

For a one-time cleanup, Excel’s built-in tools may be enough. If the data is in a structured table and every real record has a value in a reliable key column, use the filter on that column to show blank entries, review the visible rows, and delete the appropriate worksheet rows. This is useful when “blank” means “the key field is missing,” not necessarily when every field in the row is empty.

Another option is Home > Find & Select > Go To Special > Blanks after selecting a range. This selects blank cells, not rows that are wholly blank. If you select several columns, a row with one empty field will have a blank cell selected even if other fields contain data. Choosing Delete Sheet Rows at that point can therefore remove valid records. Use this route only when the selection and deletion rule have been checked carefully.

For repeatable data preparation, Power Query can remove rows that are blank in selected columns without writing VBA, and it can refresh the cleanup when source data changes. The available choices depend on how the query is structured; inspect the preview and define which columns determine whether a row is empty. Unlike a macro that deletes worksheet rows in place, a query produces a transformed output, which can make the original source easier to preserve.

Common problems and checks

Nothing gets deleted: Check that the active sheet is the intended one, the data is in the configured columns, and the cells do not contain spaces or other invisible characters. Spaces have a length greater than zero, so this macro keeps those rows.
Valid rows disappear: The tested range may be too narrow, or the macro may be deleting worksheet rows that contain information in columns outside A:F. Restore from the backup and adjust the range before running it again.
Rows with formulas remain or are removed unexpectedly: This macro evaluates cell values. A formula returning a nonempty result counts as content; one returning an empty string counts as blank. A formula that displays a space is not empty under this test.
The header is deleted: The provided loop preserves row 1. If you changed the loop boundary, verify that it starts below the actual header row.
The sheet is protected: Excel may prevent row deletion on a protected worksheet. Follow your workbook’s protection process before running a deletion macro; do not remove protection without permission.

If rows contain nonbreaking spaces or other characters that look invisible, a plain value-length check may not identify them as blank. Inspect representative cells before broad cleanup. It is often safer to create a helper column or work on a copy when the source has been imported from a website or another system.

Frequently Asked Questions

Can VBA delete rows that look blank but contain formulas?
Yes. The supplied macro reads each cell’s result, so a formula that returns an empty string is treated as blank. A formula returning text, a number, or an error is treated as content and prevents deletion of that row.

Can I delete rows that are blank in one column only?
Yes, but that is a different rule. Filter the chosen column for blanks and review the rows before deleting them, or change the macro’s test to inspect only that column. Do not use the all-columns rule if the intended criterion is simply a missing ID or date.

Does the macro delete hidden rows too?
Yes. The loop checks row values regardless of whether a row is hidden or filtered out. Clear filters or inspect hidden rows first if you want to review exactly what will be affected.

Can I undo a VBA row deletion with Ctrl+Z?
No. Running a VBA macro clears Excel’s normal undo history, so Ctrl+Z will not restore rows deleted by the code. Save a copy before running it; if needed, close without saving and reopen the original copy.

Will this work in Excel for the web?
No. Excel for the web can open and edit many macro-enabled workbooks, but it does not run VBA. Use the desktop Excel application for Windows or Mac to run this macro.

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.