VBA Delete Row If Cell Is Blank: Safe Methods

To delete rows when a cell in a chosen column is blank, loop through the data from the bottom row upward and delete each matching worksheet row. In Excel for Microsoft 365 on Windows, the VBA example below checks column A, treats empty cells, formulas returning an empty string, and ordinary spaces as blank, and keeps the header row.

What “blank” means in Excel

A cell can look empty without actually being empty. It may contain nothing at all, a formula that displays an empty string, or spaces that are difficult to see. Those cases matter because a macro that checks only for an empty cell may not catch the other two.

The example in this article uses a trimmed text check. It regards a cell as blank if its value is empty after ordinary leading and trailing spaces are removed. It also checks for Excel error values first, so a cell containing an error such as #N/A is not mistaken for a blank and does not cause a type-conversion error. A numeric zero is not blank.

Decide which meaning you want before deleting anything. If only genuinely empty cells should count, use a stricter test such as IsEmpty; that test does not treat a formula returning "" as an empty cell. If cells may contain tabs or nonbreaking spaces, ordinary VBA Trim may not remove them, so those values need separate cleanup or a more specific test.

VBA macro to delete rows when column A is blank

This example assumes row 1 contains headings, the cells to check are in column A, and column B is a dependable field that is filled for each record. Change the worksheet name and columns to match your workbook. The macro checks column A but uses column B to find the last data row, so blank cells in A do not prematurely stop the scan.

Sub DeleteRowsWhenColumnAIsBlank()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim rowNum As Long

    ' Change Data to the name of your worksheet.
    Set ws = ThisWorkbook.Worksheets("Data")

    ' Column B should contain a value for each data record.
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

    ' Work upward so deleting a row does not skip the next record.
    For rowNum = lastRow To 2 Step -1
        With ws.Cells(rowNum, "A")
            If Not IsError(.Value2) Then
                If Len(Trim$(CStr(.Value2))) = 0 Then
                    ws.Rows(rowNum).Delete
                End If
            End If
        End With
    Next rowNum
End Sub

The macro deletes the entire worksheet row, not just the value in column A. For example, if A7 is blank, all of row 7 is removed, including its values in columns B, C, and beyond. That is usually right for a table of records, but it is a consequential choice: copy the workbook or save a separate backup before running the macro. VBA actions generally cannot be reversed with Ctrl+Z.

Why the loop runs from bottom to top

Deleting a row shifts all rows below it upward. If a macro scans downward, deleting row 5 moves the former row 6 into row 5; the loop may then advance to row 6 and skip the moved record. A loop that starts at the bottom and counts down avoids that problem: deleting a row does not change the row numbers of the unprocessed records above it.

The line For rowNum = lastRow To 2 Step -1 sets the starting point and direction. The endpoint of 2 protects the header in row 1. If your headings are in a different row, adjust that endpoint and the starting data row accordingly. For example, if records begin in row 5, use 5 as the lower limit. If there is no header, use 1.

Using a reliable anchor column to find lastRow is important. If column B can also be blank in the last records, the macro may stop too early. Choose a column that is populated throughout the data, or calculate the final row using a different known boundary. End(xlUp) finds the last nonempty cell in that column; it does not identify the last row based on every column in the sheet.

How to add and run the macro in Excel

Save a backup copy of the workbook. If you want to keep VBA in the working file, save it as an Excel Macro-Enabled Workbook (.xlsm).
Open the workbook in the desktop version of Excel. Press Alt+F11 on Windows to open the Visual Basic Editor. On a Mac, the editor shortcut can depend on the keyboard and function-key settings; use Tools > Macro > Visual Basic Editor if the shortcut is unavailable.
In the Visual Basic Editor, choose Insert > Module, then paste the code into the module.
Replace Data with the exact worksheet tab name and confirm the checked column, anchor column, and first data row.
Return to Excel and run the macro from Developer > Macros, select DeleteRowsWhenColumnAIsBlank, and choose Run. If the Developer tab is not shown, you can open the macro list with Alt+F8 on Windows. On Mac, use Tools > Macro > Macros.

Excel for the web does not run VBA macros. Open the workbook in desktop Excel to execute this code. On a Mac, VBA is available in desktop Excel, although some editor controls and keyboard shortcuts differ from Windows. Macro security settings may also prevent a macro from running; only enable macros in files you trust.

Change the column or the blank test

To check another column, replace "A" in ws.Cells(rowNum, "A") with the column letter you need, such as "C". Keep the anchor column in the lastRow line separate if it serves a different purpose. If both the check and anchor fields are in column C, you can use C in both places, provided C is filled for records whose blank status you need to inspect. A dependable anchor is often a record ID or date column.

For a test that counts only truly empty cells, replace the inner condition with If IsEmpty(.Value2) Then and put the row deletion inside that condition. This will not delete a cell containing a formula that returns an empty string, because the formula is still present. Conversely, the example’s Len(Trim$(CStr(.Value2))) = 0 treats a formula result of "" and ordinary spaces as blank. Pick one rule deliberately; there is no single definition of blank that suits every worksheet.

To make the macro reusable for different worksheets, you could ask the user to select a range or pass a worksheet and column into a procedure. For a first macro, explicit worksheet and column references are easier to inspect and reduce the risk of deleting data from whichever sheet happens to be active.

Delete entire rows or clear only the cells?

Use ws.Rows(rowNum).Delete when each worksheet row represents one complete record and the whole record should disappear. Deleting an entire row shifts cells below it upward and can affect formulas, references, and other data positioned on those rows. Review the whole sheet layout first, especially if unrelated notes or summaries share the same rows.

If the goal is only to remove the blank-looking value while preserving the record, clear the checked cell instead. For example, change the deletion line to ws.Cells(rowNum, "A").ClearContents. This leaves the row in place and removes contents from column A only; formatting remains. Clearing is not equivalent to deleting a record, and it may leave a partially completed row that downstream calculations still use.

Tables and filtered lists deserve particular care. Deleting a worksheet row can shift other worksheet content, while deleting a table row changes the table itself. If the data is an Excel table, confirm that the intended rows are the only content occupying those worksheet rows before running a whole-row deletion macro.

Alternatives and when to use them

A macro is useful when you repeat the same cleanup or need a rule that distinguishes formulas, spaces, or particular columns. For a one-time task, Excel’s filter tools may be easier to review: filter the relevant column to show blanks, inspect the visible records, then delete only the intended rows. Filtering makes the candidate rows visible before removal, but take care to select entire visible rows rather than deleting just the contents of the filtered cells.

You can also sort or filter a copy of the data and copy only the records you want to keep to a new range. That approach preserves the original as a reference and can be safer when the blank rule is uncertain. It is especially helpful when a “blank” might mean a missing ID, an empty formula result, or a field that should be optional under some conditions.

Choose a VBA loop for repeatable cleanup and a clearly defined blank rule.
Choose filtering for a one-time cleanup where you want to review each candidate.
Choose a copied output range when preserving the source data is more important than editing it in place.
Choose clearing contents rather than deleting rows when the records must stay aligned.

Common problems and checks before running

If the macro reports “Subscript out of range,” the worksheet name in Worksheets("Data") probably does not exactly match a tab name in the workbook containing the code. If it finds no rows to delete, check that the first data row is correct, the chosen column is the one you intend to test, and the anchor column actually reaches the records at the bottom.

Rows that look blank but remain may contain spaces, tabs, nonbreaking spaces, or formulas that return a character other than an empty string. The sample handles ordinary spaces at the start and end of a value, but it does not remove every invisible character. A cell containing an Excel error is deliberately left alone. Inspect those cases rather than broadening the deletion rule without checking the data.

Before running on a large or important sheet, test the macro on a duplicate workbook or a small sample. Confirm that it preserves the header, deletes the expected records, and leaves formulas and adjacent data in the intended state. If formulas refer to deleted rows, Excel usually adjusts references as rows are deleted, but that does not guarantee the resulting calculation is what your workbook’s workflow requires.

Frequently Asked Questions

Can VBA delete a row if a formula returns an empty string?
Yes. The sample converts the cell’s displayed value to text and checks whether the trimmed result has zero length. A formula returning "" therefore meets the sample’s blank rule, unlike a strict IsEmpty check.

Does this macro delete the whole row or just the blank cell?
It deletes the entire worksheet row with ws.Rows(rowNum).Delete. To keep the row and remove only the checked cell’s value, use ClearContents on that cell instead.

How do I skip the header row?
The loop ends at row 2, so row 1 is not checked or deleted. Change the lower limit to the first row of your actual data if your headings or introductory rows use a different layout.

Why does VBA loop backward when deleting rows?
Deleting a row shifts the rows below it upward. Counting down prevents the loop from skipping a record that moves into the row just deleted.

Can I undo a VBA row deletion with Ctrl+Z?
No. Excel’s normal undo history does not reliably undo actions performed by a VBA macro. Save a copy before running the code, and test it on duplicate data if the deletion matters.

Choose the blank rule before deleting

The key decision is not just which column to inspect, but what your workbook considers blank and whether a whole record should be removed. Once those rules are clear, a bottom-up loop, a reliable last-row anchor, and a backup make the cleanup more predictable. Review the result before saving over the original, especially when formulas, tables, or other content share the affected rows.

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.