# Microsoft Office Application Help - Excel Help forum > Excel General >  >  Delete all the extra blank rows at the bottom of spreadsheet

## jcjcjcjc

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?

----------


## Mark@Work

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.

----------


## VBA Noob

Does this link work for you ??

http://www.contextures.com/xlfaqApp.html#Unused

VBA Noob

----------


## harrywaldron

Hi JC - I experimented some myself (e.g., trying the highlighting of cells and pasting or paste special - and it didn't copy the formulas across).

However, I'm using Office 2003, which has the "Office Clipboard".  When I copied the full range of desired cells and when I pasted using the "Office Clipboard", I also got the formulas to indeed carry over  :Smilie: 

I'm not sure if this will help you, but I'll share and hopefully other members might have a better answer.

----------


## shadestreet

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

----------


## harrywaldron

Thanks Shadestreet ... *That is an awesome technique*, that I just captured in own Tips document, I've started keeping in Word.  :Smilie:   :Smilie:   :Smilie: 

I just put this to work on fancy Excel phone listing for our company.  The size of the spreadsheet went from 888KB to 64 KB, for exactly the same reason our original poster shared ... Note that *this works going across columns as well when you're on the last column of applicable cells, you can use your technique with the Right arrow to highlight and delete anything in that direction as well*.  This way you can clean up both vertically and horizonally any propogated cells that are out-of-range.

----------


## jcjcjcjc

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??

----------


## harrywaldron

Hi JC - If Excel highlights the rows, you should be able to right mouse over on the ROW # itself and from the right mouse button select DELETE from the right mouse menu.  

If you can highlight all remaining rows, try also from the EXCEL main menu bar, >>> EDIT >>> DELETE ... As that worked in a quick test of the 888KB spreadsheet moving to 64KB

Note also that the 65,000+ rows won't physically disappear from Excel, they'll simply be cleared out and when you save, you should recover space if there were hidden attributes or data propogated downwardly in them.

----------


## Atomic_Vagabond

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.

----------


## curls

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.

----------


## fdmiles

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

----------


## trailmug

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.

----------


## Ladycane

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.

----------


## Tromso1

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.

----------

