Forum Discussion
Delete Empty Rows Quick?
How do I remove the blank rows without deleting everything? If I do it your way everything gets deleted because every row has at least one blank cell.
Let's say you have data in columns A to T, with headers in A1:T1.
Enter the following formula in U2 (i.e. in row 2 in the first empty column):
=SUM(--(A2:T2<>""))
Fill down to the last used row. The formulas will return 0 in rows that are completely empty in columns A to T.
You can filter column U to display on;y the 0 results.
Delete the visible rows, then turn off the filter.
Thanks for taking the time to explain. I found a much better and quicker solution using a macro on another post. All I do after entering data each time is click the formula and it's all done in one click.
Here is an example. For simplicity I used data in columns A to D only, and formulas in column E. I entered the word Filter as a dummy header in E1, and =SUM(--(A2:D2<>"")) in E2, then filled down.
Select any cell in the used range.
On the Home tab of the ribbon, in the Editing group, select Sort & Filter > Filter.
This adds filter drop-down arrows to the header row.
As the screenshot shows, you can also press Ctrl+Shift+L to activate the filter arrows.
Click the filter arrow in the top cell of the formula column - E1 in my example.
Clear the check box (Select All), then select the check for for 0.
It should now look like this:
Click OK (the OK button is not shown in these screenshots, it is at the bottom of the drop-down menu).
All rows with at least one non-blank cell in columns A to D will be hidden, only the empty rows remain:
Now select all rows except the top row with the headers.
Right-click anywhere in the selection, and select Delete Row from the context menu.
Finally, select Sort & Filter > Filter or press Ctrl+Shift+L to turn off the filter.
The rows with data will be displayed again.
If you wish, you can now delete or clear the column with the formulas (column E in this example).
I saw this solution, but no explanation on how to "filter column U to display on;y the 0 results."