FREE Excel Tips Newsletter from TheExcelAddict.com
Subscribe here to get more tips like this every week

The Excel Addict - Help with Excel 2013, 2010, 2007, 2003
February 4, 2016

Greetings from The Excel Addict
Hi fellow Excel Addict,

Francis Hayes (TheExcelAddict.com)What a crazy-weather winter this has been. We have had three big snowfalls in the past two months (total of 120cm/4ft), yet there is still no snow on the ground. And later today it's supposed to be unseasonably warm (10C/50F), although when I was out for a run this morning, there was freezing rain falling. It's hard to believe that the first day of spring is just 45 days away. Although, in this part of the world, I'm not ruling out getting more snow in the spring than we've had in the winter.

I hope you'll find that today's tip is helpful and saves you lots of time.

Please feel free to share it with anyone you think could use a little Excel help.

Take care and keep on Excelling,
Francis Hayes (The Excel Addict)
Email: 
fhayes[AT]TheExcelAddict.com





If you missed my last newsletter, you can click here to view it online.


Mynda's FREE Excel Dashboards Webinar


TheExcelAddict.com Quote of the Day

“A sense of curiosity is nature's original school of education.”

-- Smiley Blanton --

If you have a favourite quote, send it to me and I may post it in my newsletter.


Today's Microsoft Excel Tip

Delete Hundreds of Blank Rows in Seconds

Summary
When you have a large number of blank rows scattered throughout your data and you need to remove them, there are multiple ways to accomplish this.

I am offering you what I believe is one of the easiest and fastest ways, using a little-know trick for quickly selecting just the blank cells. I will also include an alternate method of using Filtering to delete blank rows.


Details
Over the years, one of the most frequently question asked by my readers has been, 'How can I quickly delete blank rows that are scattered throught my data?"

Most Excel users need to do this often, so it will be very helpful for you to know this trick.

This method assumes you want to delete rows based on blank cells in one specific column.

1) Select a single column of your data that contains the blank cells;

2) Press the F5 key on your keyboard to open the Go To dialog;

3) Click the Special... button;

4) In the Go To Special dialog, select the Blanks option and click OK. Now, only the blank cells from your original range are selected;

Select Special Blank Cells in Microsoft Excel 2003, 2007, 2010, 2013, 2016, 365

4) 
If you have the Delete Rows button added to your Quick Access Toolbar, it can replace the three clicks in this step with a single click. Otherwise, right click either of the selected cells and click Delete.... From the Delete dialog box, select Entire Row and click OK. All rows that included blank cells from the original selection have been deleted.

Delete Or Remove Blank Rows in Microsoft Excel 2003, 2007, 2010, 2013, 2016, 365

An alternate method for deleting blank rows using Filtering

1) If there are entire blank rows or columns in your data, you must first select the entire table. Otherwise, select a single cell in your table;

2) From the Data tab click the Filter command. Your entire table should now be selected with filter arrows at the top of each column. If the entire table is not selected, click the Filter command again to remove the filters. Manually select the entire table and click the Filter command to re-apply the filter arrows;

3) Click the filter arrow at the top of a column containing blank cells;

4) Uncheck the (Select All) option at the top of the filter list (you may need to scroll up to see this option). This will deselect all items from the filter list;

5) Scroll to the bottom of the filter list, choose (Blanks) and click OK. Now only the rows containing blank cells in that column are visible;

Select Blanks In Filter in Microsoft Excel 2003, 2007, 2010, 2013, 2016, 365
6) Select the filtered/blank rows, excluding the column headings;

Select Only Visible Blank Cells In Filter in Microsoft Excel 2003, 2007, 2010, 2013, 2016, 365
7) On your keyboard, press ALT+; (semicolon) to select the 'visible cells' only;

8) If you have the Delete Rows button added to your Quick Access Toolbar, it can replace the three clicks in this step with a single click. Otherwise, right click either of the selected cells, click Delete Row... and click OK on the 'Delete entire sheet row?' message that pops up.  All of the blank rows have been deleted, however the non-blank rows are still filtered, therefore not visible.

Delete Blank Rows Filtered in Microsoft Excel 2003, 2007, 2010, 2013, 2016, 365

9) On the Data tab click the Filter command to remove the filter arrows and reveal the non-blank rows of data.

All Blank Rows Have Been Deleted in Microsoft Excel 2003, 2007, 2010, 2013, 2016, 365


Thanks for supporting this newsletter and website

FREE Excel Tips Newsletter from TheExcelAddict.com
Subscribe here to get more tips like this every week
"Spreadsheets Tips From An Excel Addict" is a weekly publication of TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630