How to Remove Blank Rows in Excel (Without Deleting Half Your Data)

The "I deleted half my data" problem

Every Excel user learns the F5 > Special > Blanks > Right-click > Delete > Entire Row workflow at some point. And every Excel user, sooner or later, runs it on a sheet with any blank cells anywhere and ends up with half their rows gone.

The bug isn't in your workflow — it's in F5. The Special > Blanks selection picks any cell that's blank, then "Delete > Entire Row" deletes every row that contains a selected cell, even if only one cell in the row was blank. So a row with data in 9 columns and one blank gets deleted along with the truly-empty rows.

Here's how to do this safely. Three methods that don't accidentally clobber your good data.

TL;DR — Key takeaways

• F5 > Special > Blanks > Delete Entire Row is dangerous: it deletes any row that has any blank cell, not just rows that are entirely blank.
• The Filter method (filter to "(Blanks)" on a known-required column, delete visible rows) is safer but only catches blank rows you can identify with a single column filter.
• A VBA macro is the right answer when you want every row where every cell is blank deleted, with no false positives.
• Always select the full width of your data when running any blank-row remover, not just a sample column.

What "blank row" should actually mean

A row is "blank" if every cell in your selection is empty. That's the definition that doesn't destroy data.

The F5 trick uses a different definition: a row contains blank cells. That's why it's dangerous. A row of 12 columns with data in 11 and a blank in column 4 contains a blank cell, so F5 selects that blank cell, and "Delete Entire Row" deletes the whole row including the 11 columns of data.

Method 1: Filter method (safe but limited)

If you know that a particular column should always have a value in real rows, filter to find the blanks.

1. Click any cell in your data, then Data > Filter to enable the filter row.
2. Click the filter dropdown on a column where every real row should have a value (e.g., Customer Name or Order ID).
3. Uncheck everything except (Blanks).
4. Excel now shows only rows that are blank in that column.
5. Select all visible rows, right-click on the row number, Delete Row.
6. Clear the filter.

The catch: this only catches rows that are blank in the column you filtered. A row with no data in any column except a stray comment in column 12 won't be caught.

Method 2: Sort by a known-required column

Similar idea, different mechanic.

1. Sort the data by a column where every real row should have a value, ascending.
2. Blank rows in that column cluster at the top or bottom.
3. Select the contiguous block of blank rows.
4. Right-click > Delete Row.

The catch: you've now reordered your data. Re-sort by your original sort key when done. And again, a row with stray data in any column won't necessarily cluster.

Method 3: F5 > Special > Blanks (the dangerous one)

Just so you know what NOT to do:

1. Select your data range.
2. Press F5 > Special > Blanks.
3. Right-click any selected cell > Delete > Entire Row.

This deletes any row that has any blank cell, not just entirely-blank rows. It works correctly only on data where every column is required and any blank is by definition a blank row. That's a narrow case.

If you've already run this and lost data, Ctrl + Z immediately. Don't save the file. Don't take any other action that pushes Z further down the undo stack.

Method 4: The free VBA macro (only deletes entirely-blank rows)

Download Remove Blank Rows. Free .xlsm with one macro.

Steps:

1. Select the full range of your data, including every column.
2. Press Alt + F8, pick RemoveBlankRows, click Run.
3. The macro walks each row in your selection and deletes any row where every cell in your selection is blank or whitespace-only. The popup reports the count.

The macro's blank-row definition is "every cell in the selection is empty or whitespace-only." So a row with even one cell of real data survives. This is the safe behavior the F5 trick lacks.

A common scenario: cleaning a CSV from the supplier portal

The portal CSV has a blank row between each vendor block (about 140 separator rows in a 3,200-row file). Every real row has data in every column.

1. Select the entire used range (Ctrl+A inside the data block).
2. Run the Remove Blank Rows macro.
3. The 140 separator rows disappear in 1 second. The popup confirms the count.

Compare to the F5 trick on the same file: any blank cell in any real row would have caused the macro to also delete that row. Probably 30 to 80 rows of legitimate data lost depending on how complete the source was.

What if my data has columns I want to ignore for the blank check?

Run the macro on a narrower selection. The macro only checks cells inside your selection. So if you select rows 2 through 3000 across columns A through E, the macro deletes any row where A-E are all blank, even if columns F or G have data in those rows. That's almost never what you want. Always include the full width of your data.

What about rows that look blank but contain a formula evaluating to empty?

A formula cell evaluating to "" is treated as blank by the macro, so a row of formula cells all evaluating to "" would be deleted. If you don't want that, copy the formulas to values first or run on a narrower selection that excludes the formula columns.

Frequently asked questions

What about whitespace-only rows (cells contain only spaces)?

Treated as blank. The macro uses Trim$(CStr(value)) = "" to detect blanks, so whitespace-only cells count. This is the right behavior because whitespace-only cells are almost always accidents.

Will it preserve formatting on rows it doesn't delete?

Yes. The macro only deletes rows where every cell is blank. Rows with any data are untouched, including their formatting.

Can I run it on multiple non-contiguous selections?

The macro reads the selection as a single rectangular range. If your selection is non-contiguous (Ctrl+click), behavior is unreliable. Run the macro once per contiguous block.

What if my selection has merged cells in some rows?

Merged cells make Excel's row deletion behave oddly. Run Unmerge Cells and Fill Down first to flatten merges, then run the blank-row remover.

Can I undo it?

Yes, Ctrl/Cmd + Z right after running undoes the deletions in one step. As always, work on a copy of the file if it's the only copy.

What to do next

After removing blank rows, the sibling question is empty columns: see Delete Empty Columns to remove those in one pass too. If your data still has gaps inside otherwise-real rows, Fill Blank Cells With Value Above can carry forward category labels into the gaps.