Hi fellow Excel Addict,
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 (10°C/50°F), 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)
If you missed my last newsletter, you can click here to view it online.
If you have a favourite quote, send it to me and I may post it in my newsletter.
Delete Hundreds of Blank Rows in Seconds
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.
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;
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.
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;
6) Select the filtered/blank rows, excluding the column headings;
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.
9) On the Data tab click the Filter command to remove the filter arrows and reveal the non-blank rows of data.
|"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