Deleting unwanted rows in Excel worksheet

In an Excel 2010 file, there are about 100 rows containing data, but the whole work sheet displays 1048576 blank rows (making the file about 2.5 mb). I need to delete blank rows after the data. But selecting the row and deleting it is doing nothing. How to delete these unwanted rows?

This is my excel file: http://www.mediafire.com/download.php?au957fcnh3odbcd

Can someone explain what is wrong with this file?

There is definitely something wrong with your file. It might be easier to copy the 100 rows you want to keep to a new workbook file rather than try to delete a million rows.

A search for duplicates in the whole document found and removed over 1 million duplicates. So there are hidden characters or something occupying space in that workbook. That still didn't help because the save size after that went up to 35MB.

Solution: Once I copied the rows you have populated to a new workbook, the saved file size is only 10K.

The commenter on Robert's post is right on target. After deleting the unwanted rows you need to save the workbook, close, and then re-open it.

Microsoft outlines the steps in their "How to reset the last cell in Excel" article. They also mention the Save step, but I wish they'd give it more emphasis. Until you save and re-open the workbook, it doesn't look like your deleting has done anything!

Copying the good rows to a new workbook is also a good solution, especially if you don't have any macros or formulas with complicated cell references to worry about.

Select the rows you want to delete. If you have Office 2003: http://www.mrexcel.com/archive/Edit/4259.html

2007 and later: On the Ribbon, under the "Home" Tab, under the "Cells" Group and "Delete" Button (block called cells), select the small arrow under it then select "Delete Sheet Rows". Save file and Reopen File.

Another cause can be that one or several columns of the worksheet are referenced in some formula in another worksheet without specifying the number of rows.