Reduce a sparsely populated range in Excel

The only way I have done this before is to insert another 'key' column (numbers 1 - n) alongside the sparse range. This allows you to reorder the data in its original sequence after you have sorted out the blanks.

While I agree with Richard, there is no Edit menu in Excel 2007. You need to be in the Home tab on the ribbon and click the Find & Select button. Select Go To Special, check Blanks, and click OK. You can now delete the selected cells.

Highlight the range. Use Goto (F5) Special Constants. Edit Copy . Click in an unused area of the spreadsheet. Edit Paste. Now, if desired, copy the result back over the origin.

"You have not mentioned what version of Excel you are using.

Apart from all the answers given here, you could try the 'Removing Duplicates Option' which will remove all the blanks and leave you with one blank cell. It will remove the row also which contain the blank.

Here is how you go about it:

Ribbon > DATA Tab > Remove Duplicates

NOTE: Use this with caution, as this will also delete all other non-blank but duplicate cells. I provided this response with respect to your example."