Excel: Remove Blank Rows from a Range

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

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.

  1. Remove blank rows.

  2. 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.

  3. 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.