Often when working with data especially data that is unformatted, such as the information I export from the MLS, I will receive a large number of blank rows interspersed with the information I am interested in. One solution would be to just sort the data and the blanks will be at the bottom or top of the list depending on the sort order. Now suppose the order of the data is as important as the data within the list well the technique below will allow you to delete the blank rows while preserving the order of the list.

Before you begin please make sure you save your workbook.

  1. Step one select the range of cells you want to remove blanks from
  2. Press F5 on your keyboard or click Edit and Goto
  3. Click the Special button
  4. Click the Blanks option and click OK.
  5. If you are using Excel 2007 choose click the arrow under delete on the ribbon and choose Delete Sheet Rows. If you are using Excel 2003 or Excel XP right click click delete and select entire row.

Step 1
list11.jpg

Step 2

select_blank2.jpg

Step 3

select_blank3.jpg

Step 4

select_blank4.jpg

select_blank4b.jpg

Final Product

select_blank_final.jpg

– Mark Flavin

3 Comments

  1. Missy Caulk on March 8, 2008 at 7:53 am

    Thanks for the help, Loren. I am excel challenged, my friends laugh at me.

  2. Mark Flavin on March 8, 2008 at 10:09 am

    No problem Missy, there are a lot of features to excel and it is not easy to know them all, I promise not to laugh at you. If you have any questions about how to do something in Excel just leave a comment and I will write on it.

  3. Andrew on December 5, 2011 at 7:40 pm

    Hi Mark,

    I liked the Excel Help information on this blog.

    1) Very well explained.

    2a) You gave an Easy solution that I didnt think of using the Data/Sort command.
    2a) Then you gave a more Excel-centric solution that leveraged some of Excels less well known menu commands.

    3) And the explanations and “how tos” and “screenshots” were well written & easy to understand.

    4) By far this is 1 of the best Excel Help blogs I have used.

    Thanks,
    –Andrew , CA —

Leave a Comment