Delete all the extra blank rows at the bottom of spreadsheet

I have a worksheet that has 156 rows in it, someone has scrolled down the sheet an it seems that they have inserted 45,000 empty rows. This has made my spread sheet size large. I have tried to delete the rows at the bottom of the sheet, been onto google and found some macros to run. The macros run ok and delete any empty rows if they are in the first 156 rows but they dont seem to delete all the empty rows at the bottom of the sheet.

I could cut and paste the worksheet but it is quite a complicated one with lots of formatting and formulas.

There must be a simple solution, can anyone help?

Sorry I can't help, but I just wanted to add my own moan about this feature of EXCEL.

It seems to keep a record of cells that have been "altered"
and that seems to include cells that have been deleted.

If a clean cell at the bottom of a new workbook is selected and deleted,
that workbook now has that number of rows.

What we want is some way of resetting the "altered" flag.

1. left clik on row 157 (or the first empty row you want to delete after your data)
2. hold shift+ctrl and then press down
3. All rows (approx 64,000) should be highlighted
4. Right click and delete all the rows
5. Save your workbook


This should work

thanks to everyone who replied to me so far. Thanks Shadestreet that looks like a nice easy solution (and it worked for some), however i tried it and it didn't delete the rows (i've done what you said a number of times but couldn't get it to work) Could this because i'm using Excel from Office 2000??

Does anyone have a solution for this that will work on a very low-powered machine? I work in a warehouse office that has us running off these tiny box computers (8"x8"x2").

I made the mistake of copy-pasting a formula to an entire column instead of just the rows I need. My spreadsheet goes down to row 1,048,576 and I get an error reading that Excel cannot complete the command for the above fix with available resources.

I tried deleting by column instead of row, put that ended up removing my data as well.

8 years later and this posting saved me! I wanted to print, and those 50k extra lines made a mess
of the print preview (4000 printable pages...). Tried the fix, it worked and now the print preview
is 6 pages.

Thank you to everyone here!


On using low use computers -- can you select 5000 at a time, and clear all in them? That would probably work.

I found a very quick method.... assuming your good rows don't have any duplications, highlight the entire spreadsheet and in DATA select Remove Duplicates. works a charm for me

Please note that, in Excel 2010, "Save your workbook" is a vital step. The last row in the worksheet isn't change until the file is saved.

I've found the easiest solution is just to create a new worksheet and copy just the data into a new worksheet. Delete the original worksheet.

First find out where Excel thinks the last currently-used cell is. To do this :-

Press function key F5 - a "Go To" panel will be displayed.
Select "Special...", then "Last cell", then "OK" - the "Go To" panel will disappear, and the "last used cell" will become highlighted.

Highlight all the empty rows (don't forget to include the row number "cells") below your useful data up to and including the row containing the "last used cell" and delete them.

IT IS IMPORTANT TO PERFORM THE NEXT STEP IMMEDIATELY BEFORE DOING ANYTHING ELSE!!
Left-click anywhere in the useful data, then Save the spreadsheet, Close it, and Open it again.

Repeat the first paragraph and you should see that Excel now thinks that the "last used cell" is the rightmost cell of the last useful row.

You can delete unused columns to the right of useful data in a similar way.