Remove Blank Rows
How wide you select before opening Go To Special decides whether this removes the empty rows or eats rows that still hold data. Blanks marks every empty cell inside the selection, so highlighting the full multi-column block picks up a row carrying an order number and a date but no amount, and the deletion that follows takes it away with the genuinely empty ones. Highlight one column that is populated on every real row instead and the selection cannot reach a row holding anything. On very large sheets, sorting or filtering that same column is safer still, because the blanks gather into one visible block you can inspect before deleting rather than after.
The formula
F5 > Special > Blanks > OK selects the empty cells inside whatever was highlighted first
Home > Delete > Delete Sheet Rows removes whole rows; Delete Cells shifts and misaligns instead
=COUNTA(A2:F2)=0 helper column: TRUE only when the ENTIRE row is empty
=FILTER(A2:F500, BYROW(A2:F500, LAMBDA(r, COUNTA(r)))>0) Excel 365: a copy with the blank rows dropped, source untouchedA worked example
A2:F500 is an export in which 60 rows are completely empty and another 40 hold an Order ID and a date but nothing in Amount, Region or Rep. Column A holds the Order ID and is populated on every real row.
Select A2:A500 alone > F5 > Special > Blanks > OK > Home > Delete > Delete Sheet Rows
60 rows go, 440 remain, and the 40 partly-filled rows are untouched. Run the identical steps with A2:F500 selected instead and 100 rows disappear: Go To Special found the empty Amount cells on those 40 rows, and Delete Sheet Rows never asks whether the rest of the row was empty. Ctrl+Z reverses either version in the same session; after a save and a close, neither.
Which one do I need?
Frequently asked questions
Why did Go To Special delete rows that had data in them?
Because the selection was more than one column wide. Blanks marks every empty cell inside the highlighted range, and Delete Sheet Rows then removes the whole row belonging to each of them — including a row that was three-quarters full and merely had one gap. Highlight a single column that is populated on every genuine row before opening the dialog and the selection cannot reach a row that holds anything.
Why does Ctrl+End still jump to row 50,000 after I deleted the blanks?
Excel caches the largest area a sheet has ever occupied, and deleting rows does not reset that cache on its own. Delete the rows, save the workbook, close it and open it again: the used range is recalculated on load. Until then the scroll bar, the print area and the file size all still describe rows that hold nothing.
Why will Go To Special not find rows that look empty?
Those cells are not empty. One typed space, a non-breaking space pasted in from a web page, or a formula whose result is "" all count as content, and Blanks correctly passes over them. Test a suspect cell with =ISBLANK(A5); FALSE means something is in there. Find and Replace with a single space in the Find box clears the first two cases, while the formula case needs the results pasting back as values first.
Can I get the original row order back afterwards?
Only if you prepared for it. Sorting to gather the blanks rewrites the sequence permanently, so number a spare column 1, 2, 3 down the side first and you can always sort back to it. The Go To Special route reorders nothing, which is the reason to prefer it whenever the sheet is already in an order that means something.
New guides and tools, once a month
DE + EN · double opt-in · no spam