One sheet made my file go from 3000 kb to 35,000 kb

[LeeBillington]

Hi guys, is there anyway to find out why one sheet in my workbook has suddenly made my excel workbook go from 3,000 to 35,000?

like anyway to find out what is causing the influx in kb?

[shg]

First, CLEAR, then DELETE all unused rows and columns.

Then save, close, and reopen.

[LeeBillington]

Every time i select some rows i believe to be empty then clear contents or delete, i get "Excel cannot complete this task with available resources. choose less data or close other applications. continue without undo?"

Its like all 1,048,576 rows are scrollable like its there. but i cant delete or clear because of the above error.

Cant delete any single row/column , or multiple.

[shg]

So save a backup and continue without Undo.

[LeeBillington]

i clicked continue without undo and nothing happened?

[FDibbins]

Try selecting smaller chunks or rows (or columns) at a time, and delete those

[shg]

You probably need to wait awhile.

[FDibbins]

Also, maybe try deleting cell contents 1st, then deleting rows

[rondeondon]

create a new sheet
copy the data from the problem sheet into the new sheet
delete the problem sheet
save file

[shg]

Also, maybe try deleting cell contents 1st, then deleting rows

You need to CLEAR the cells first, Ford, not just clear contents. Otherwise formatting that may be extending the used range is copied from the last row/column when the rows/columns are deleted.

[FDibbins]

Good point, thanks for the reminder

[scottiex]

At one stage i wrote this to resolve this issue - ie delete a few rows at a time (then delete extra columns.
so you could use this...

others,
Since I might have your attention -
could I do this much more efficiently by instead coding it to do what shg suggested - ie clear the cells first then delete them all in one go? would that be faster and safe from crashing?
Or clear them and still delete in batches?
Sometimes it takes longer than I would like.

But don't let me hijack the thread...

Please Login or Register  to view this content.

[Zer0Cool]

create a new sheet
copy the data from the problem sheet into the new sheet
delete the problem sheet
save file

I agree with this. Its the alternative option to what has also been suggested which is to clear out the excess cells using Clear All or Delete. In especially bad sheets the amount of time you can spend whittling the sheet down to a manageable Used Range could be beyond the point of reason.

Copying just the data (as per the instructions given) you need is a faster approach but comes with some warnings. It could duplicate things like named ranges or styles (which can cause similar issues to what you are correcting) unless using paste special values, formulas, etc.

@scottiex, I would highly recommend not using VBA or any other program or scripting to attempt this cleanup. Your method for example, just like most logical attempts to solve this issue, rely on Excel knowing the difference between the content you have and the content you want. If it could do this, then we wouldnt have the problem in the first place, right?

Your code may work in examples in which the excess cells do not have any actual value within the cell, but it doesnt account for things like non-print characters, dependencies (other formulas, named ranges, tables, pivots, etc. reference the cell), etc.

This is dangerous as you are just as likely, if not more so, to delete actual content vs the excess.

There is as far as I know, no programmatic way to differentiate between valid content and excess content (accurately). You may be able to address very specific cases that are confined to very specific conditions, but there just isnt a universal way to handle all the causes/types of excess content that can cause this problem. If it was possible MS would just build it in to Excel (like they did with Inquire | Clean Excess Cell Formatting)

[scottiex]

thanks for that ZeroCool,
definitely an important consideration.