How to purge bloated sheet?

[Jan Nademlejnsky]

Is there any way to purge a bloated spreadsheet? My sheet is suddenly 2x
larger than before when saved. I cannot find any reason for it. There are no
extra formulas or external links.

I think I saw somewhere that there is an utility to purge the spreadsheet.

Thanks

Jan

 

[Gord Dibben]

Jan

Excel has a habit of retaining "used" cells way beyond where you are currently
working.

On a worksheet hit CRTL + END to see where you wind up.

If far below and to the right of your real used range then select the row
below where your real used range is and SHIFT + END + DownArrow. This selects
all rows to the bottom of the sheet. Edit>Delete>Entire Row.

Do the same for columns to the right of your range.

Repeat on all sheets.

Important step now.........SAVE the workbook. In some cases it may be prudent
to also CLOSE after Saving then re-open.

There are other ways to reset the used range. Debra Dalgleish has some code
at her website.

http://www.contextures.on.ca/tiptech.html

Gord Dibben XL2002

Is there any way to purge a bloated spreadsheet? My sheet is suddenly 2x
larger than before when saved. I cannot find any reason for it. There are no
extra formulas or external links.

I think I saw somewhere that there is an utility to purge the spreadsheet.

Thanks

Jan

Gord Dibben XL2002

 

[Jan Nademlejnsky]

Thanks Gord for your quick response.

I checked the extra cells, actually I have a button with macro to check
this. There are no extras there.

Jan

 

[Jan Nademlejnsky]

Yes, now I found that the sheet last cell is in row 61184, but my actual
last row is 554. When I delete all rows from 555 to 65553, it still shows
the last cell in row 61184. I cleared that region, delete format, look for
links, but no luck.

Jan

 

[Otto Moehrbach]

Jan
Read Gord's response again. You need to save the file, then close the file,
then open the file, to change your "last cell" HTH Otto

 

[Jan Nademlejnsky]

Thank you very much. This is the trick!

Delete all rows and columns outside of the last cell
Save it
Close it
Open it

Jan