Remove Blank Rows

What it does

Excel's built-in workflow for deleting blank rows is "F5 > Special > Blanks > Right-click > Delete > Entire Row", and it has a famous bug: it deletes any row where any cell is blank, not rows where every cell is blank. So if your data has any holes at all, half your rows vanish. The macro fixes that. It scans each row in your selection, deletes the row only when every cell is empty (or whitespace-only), works from the bottom up so row indices stay stable, and reports a clean count at the end.

Real-world example

Picture a procurement coordinator at a mid-size regional hospital system. Every Wednesday she receives a CSV export from the supplier portal with that week's delivered orders. The portal helpfully inserts a blank row between each "vendor block" so a human can scan the file. For Excel's purposes those blank rows are noise; they break SUMIFS, COUNTIF, and pivot tables.

Her current workflow: filter the column with the order number, set the filter to "(Blanks)", select visible rows, right-click, Delete Row, clear filter. About 90 seconds per file. Sometimes the filter dropdown doesn't refresh and she ends up with two blank rows back. Other times she forgets to clear the filter, sends the file to her boss, and gets a confused email asking why the file is half empty.

With this macro she selects the entire used range (Ctrl+A inside the data block), runs the tool, and the 142 blank rows in the 3,200-row weekly export disappear in about 1 second. The popup says "Removed 142 blank row(s)." She compares that to the row count delta to make sure nothing weird happened. Files goes to her boss without the manual filter dance.

On a typical 3,200-row weekly delivery export with about 140 separator rows, expect about 80 seconds saved per file. Across 50 weeks that's just over an hour back, but the real win is one less spot in her week where a filter-and-delete dance can corrupt the file.

How to use

First time? See How to run our tools for the 30-second setup.

1. Select the range of rows you want to scan. The macro only deletes rows where every cell in your selection is blank.
2. Press Alt + F8 (Windows) or Option + F8 (Mac), pick RemoveBlankRows, click Run.
3. The macro deletes every fully-blank row in the selection from the bottom up and reports the count.

FAQ

What if a row has data in some cells but is blank in others?

That row is kept. The macro only deletes a row when every cell inside the selection is empty or whitespace. So a row with one number and several blanks survives. This is the opposite of Excel's built-in F5 > Blanks > Delete behavior, which deletes any row touching a blank cell.

Will it look at cells outside my selection?

No. The macro only checks the cells inside your selected range. So if your selection is columns A through E but the row has data in column F, the macro still considers the row blank (because A to E are empty inside your selection) and deletes the entire row, including the data in column F. Always select the full width of your data, not just the columns you visually focus on.

What counts as blank?

A cell is blank if it's truly empty, contains only an empty string "", or contains only whitespace characters (spaces, tabs). So a row with a stray space in one cell is still treated as blank. If you specifically want to keep rows with whitespace, run Trim Whitespace From All Cells first to make the whitespace explicit, then decide what to do.

Does it skip rows that are only formulas evaluating to empty?

A formula that returns "" is treated as blank by the macro. This catches a common case: a calculated column where some formulas legitimately have no output. If you don't want those rows deleted, copy the formula column as values first or run the macro on a narrower selection that excludes that column.

Can I undo it?

Yes, Ctrl/Cmd + Z right after running undoes all deletions in one step. As always with deletion macros, work on a copy of the file if it's the only copy. The macro runs entirely offline. Free, hosted on GitHub, source visible.