- Excel Book Excerpt
Excel Remove Blank Rows from a Range
This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Remove Blank Rows from a Range
Problem: Someone has given me data pasted from Word. There are a number of blank cells in the list. I want to eliminate the blank rows.
Figure 685 Remove blank rows.
Figure 686 No blanks, but the data is resequenced.
Strategy: If the sequence is not important, you can sort the entire data range. Excel will move all blank cells to the bottom of the sort range. Here’s how you do it:
1. Move the cell pointer to A1. While holding down the Shift key, press the End key and then the Home key. Excel will select the entire range of data in the spreadsheet.
2. Select Data, Sort. In the Sort dialog, indicate that your data does not have a header row by unchecking the My Data Has Headers box. Click OK.
Figure 687 Uncheck the My Data Has Headers box.
Results: The blanks will be removed from the list.
Excel’s Sort feature will always move blanks cells to the end of the sort. Sorting a column with blanks is a quick way to remove the blanks from the data.
For more resources for Microsoft Excel: