Most valuable professional
  • 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.

LE10000707.jpg 

Figure 685 Remove blank rows.

LE10000709.jpg 

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.

LE10000708.jpg 

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.

<-Previous Topic                    Next Topic->

For more resources for Microsoft Excel: