How to quickly delete blank rows in a worksheet [revisited]

Two weeks ago we shared a tip on how to quickly delete blank rows in a worksheet. Thank you for the comments we received on this tip, they didn’t go unnoticed. A lot of you have requested that we show you how to delete blank rows in a worksheet without deleting rows which have data but also have a few blank cells.

When capturing data some fields can be left blank on purpose like the third line of an address, people may not fill it in, but you may not want the entire row deleted just because of that one blank cell. You can avoid deleting the entire row, and ensure that only rows that are entirely blank are deleted. Here’s how:

Note: Download the sample workbook to practice this exercise.

Applies To: Microsoft® Excel® 2007, 2010 and 2013

To make it clear we have highlighted all the blank cells within non blank rows with blue in the practice workbook.

  1. Select the entire data list:
  • Select the first data row
  • Press CTRL+SHIFT+END or CTRL +SHIFT & down arrow several times

tip-1

 

 

 

 

 

  1. Select the Data tab and Filter as shown below.

tip-2

 

 

 

  1. Then select blanks:
  • Click on the Product ID filter arrow
  • Uncheck the Select all box
  • Select (Blanks)

tip-3

 

 

 

 

 

 

 

 

  1. Highlight the filtered rows, headings should be excluded:
  • Select the first row for the filtered data
  • Press CTRL + SHIFT + END ,or CTRL + SHIFT + down arrow
  1. Press CTRL and minus.
  2. Select OK when asked to delete entire sheet row.
  3. To clear the filter select the Data tab and Filter as you had done to create the filter.

tip-4

 

 

 

8. The final result will look as follows:

tip-5

 

 

 

 

 

Only the rows that are entirely blank have been deleted. The rows which only have some blank cells have not been deleted.