If you've been looking for an easier way to
delete blank rows from your Excel data, then check this out...
Quickly Delete Blank Rows From A Long List (Excel 2010, 2007, 2003)
by Francis Hayes (The Excel Addict)
If you have a
long list of data that contains blank rows that you want
to delete, you have several options.
- You could select each row, one by one, and delete
them individually. If you have dozens or hundreds of rows, this could
be time consuming.
- You could sort the rows alphabetically, which would
put all the blank rows together, and then delete that group of blank
rows. However, there will be times when you may want to maintain the
order of your list, so this would not be an option.
- You could filter the list for blanks, select these
rows (i.e. visible cells) and delete them.
- You could use a little-known shortcut to quickly
select the blanks in your list and delete them.
This week I will cover option number 4 and next
week I will cover option number 3. The first two are self-explanitory
and usually are not your best choice, so I won't discuss those.
Suppose you receive a list of data that contains
blank rows that you want to remove without affecting the order of the
list. This technique will allow you to delete all rows that have blank
cells in a particular column. Here's the fastest way to do it.
- First, before you make any drastic changes to any
workbook always make sure you have a backup copy or a recently saved
copy in case you need to restore the original data .
- Now, select the cells in one column from the top of
your list to the bottom.
- Make sure that all the blank cells in this selected
range are the rows you want to delete.
- Press the F5 key on your keyboard (or select Edit, Goto).
- Click the Special... button.
- Click the Blanks option and
click OK. This will select all blank cells in the
range you had previously selected.
- Now choose Edit, Delete, select
the Entire Row option and click OK.
If you work with large lists of data in Excel, this
tip will save you a lot of time. If you don't work with large
lists often, remember this tip anyway, because sooner or
later you're going to need this.
Get more FREE Excel
Copyright © All Rights Reserved by Francis
Hayes (The Excel Addict)