| FREE
Excel
Tips
Newsletter
from TheExcelAddict.com Subscribe here to get more tips like this every week |
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)
Email: fhayes[AT]TheExcelAddict.com
If you missed my last newsletter, you can click here to view it online.
| 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.
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;
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;
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.
| FREE
Excel
Tips
Newsletter
from TheExcelAddict.com Subscribe here to get more tips like this every week |
Copyright Francis J. Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630