Delete Blank Rows in Excel
To delete blank rows in Excel, use Go To Special, Blanks. You can also use the COUNTA function and a filter to remove empty rows.
1. On the Home tab, in the Editing group, click Find & Select.
2. Click Go To Special.
3. Select Blanks and click OK.
Excel selects the blank cells.
4. On the Home tab, in the Cells group, click Delete.
5. Click Delete Sheet Rows.
Result:
Conclusion: this method also deletes rows with one or more blank cells. If you don't want this, keep reading.
The following awesome trick only removes rows that are completely empty and leaves rows with one or more empty cells.
6. Add a Blanks column and insert the COUNTA function.
7. Click on the lower right corner of cell H2 and drag it down to cell H10.
If the COUNTA function returns a 0, the row is "completely empty". To filter these empty rows, execute the following steps.
8. Select cell H1.
9. On the Data tab, in the Sort & Filter group, click Filter.
Arrows in the column headers appear.
10. Click the arrow next to Blanks.
11. Click on Select All to clear all the check boxes, and click the check box next to 0.
12. Click OK.
Result: Excel only displays the rows that are completely empty.
13. Delete these rows.
14. Click Filter again to remove the filter.
15. Delete the Blanks column.
Result:
