Hi guys, newbie member, so be easy on me!
I have suffered this a bunch of times and I am fed up having to start again. Basically I have a perfectly reasonable excel (2003) file which suddenly becomes massive. Generally I can track it down to being the fault of one of the sheets. With a load of searching, I have done everything I have found as suggestions. The cells have been 'deleted', they have been cleared, the formatting has been cleared. I have made sure nothing is wrong via the VBA editor. Nevertheless, my document with one 'empty' sheet occupies well over 1.5Mb. Now one obvious problem is that the sheet shows the full range of cells, right up to IV65336. This I am guessing is my problem. But how the hell do I get excel to actually accept that there is nothing active in all these cells?! In future I can avoid getting into this mess by being more careful with what I am doing but surely there has to be a way of undoing it and having excel return to a sensible state.
I would love to know just for curiosity sake but also so that I don't have to redo this whole sheet and all the references hidden within!
Thanks
Hello talksalot,
welcome to the forum. Try this
in each sheet, hit the End key, then the Home key. This will take you to the last active cell of the sheet. If this is way below your active data area, you need to delete rows and columns.
Select the first whole empty row under your data area, hit Ctrl-Shift-DownArrow, right click and select Delete
Select the first whole empty column to the right of your data, hit Ctrl-Shift-RightArrow, right-click and select delete.
Repeat for the other sheets.
Save and close the file.
Reopen and it should be much smaller.
hth
teylyn
Microsoft MVP - Excel
At Excelforum, you can say "Thank you!" by clicking theicon below the post.
Avoid pie charts with more than two data points. Why? See here (pdf, 559 kb). The only acceptable pie chart is here.
Many thanks teylyn. Unfortunately I have tried that already. I tried it one more time, just for luck, but the same result. I still have IV columns and 65336 rows showing for my scroll bars and the file size remains the same. If I select all and paste into a fresh sheet, the file size doubles and I my new sheet now has the same massive scrolling range (before the paste, the scroll bars take me only to about U39).
The problem seems to be that there is something there that I cannot delete. I have tried clearing, deleting, delete rows and columns, delete content, delete all, delete formatting.... but none of it actually deletes the extraneous material. The only way I can get rid of it is deleting the sheet which surely can't be the only solution...
thanks
did you save the file, close it and reopen?
when you hit End - Home, where does it take you?
teylyn
Microsoft MVP - Excel
At Excelforum, you can say "Thank you!" by clicking the
I did indeed. It takes me to IV:65336 and I am almost certain this is the problem. However, nothing I have done seems to change that.
Do you have macros? If so, can you post them?
Do you have named ranges? Data validation? Conditional formatting?
Hit F5 - Special - tick Data validation and All - OK to find all Data validated cells
Hit F5 - Special - tick Conditional formats and All - OK to find all CF cells
teylyn
Microsoft MVP - Excel
At Excelforum, you can say "Thank you!" by clicking the
Macros were trivial things, I deleted them entirely and that made no difference (though they were not saved on that specific worksheet anyhow, so no surprise). There were named ranges, conditional formatting and data validation but the goto now reports none of the above and I deleted the named ranges. As far as I can see, I now have a workbook with a single worksheet composed of IV:65536 cells, all with no visible content but adding up to 1.5Mb.
As I noted, I have had this happen before. There seems to be some excel remenant which I just cannot delete....
Someone suggested merged cells may cause havoc - i may well have some in there but thought the formatting would have been removed with all the deletion attempts.
Ah Now I am finding a bunch of reports of this problem. The advice seems to work for some people but there are loads of reports where it simply has not worked and no solution has been found. Starting to feel less than confident about this....
Last edited by talksalot81; 03-18-2010 at 12:02 AM.
can you zip the file and upload it here?
teylyn
Microsoft MVP - Excel
At Excelforum, you can say "Thank you!" by clicking the
There you go!Originally Posted by teylyn
I tried sorting things with asap utilities but it just gives me errors when i try and delete hidden or empty things!
I think the file is corrupt. None of the usual methods work for me.
There is a custom view in the file, but deleting it won't change anything. When I open the file as you sent it, I get a message "Data may have been lost", which for me indicates that Excel can't read everything OK.
Saving as html and then back to xls also does not reduce file size significantly.
So, I don't know what's caused it and I don't know how to repair it.
Sorry.
teylyn
Microsoft MVP - Excel
At Excelforum, you can say "Thank you!" by clicking the
No apology required! I appreciate your efforts!
The key thing is that you have told me that I am not simply an idiot and unable to follow instructions! I recall the formation of that custom view though it was a failure and I didn't even know it was still there. I don't get that message about "data may have been lost" so perhaps my version of excel is not working correctly. I will do an update and try to rule that out. However, I must assume that there is a bit of a bug in excel that allows this to happen. I do have a tendency to use 'ctrl-A' to copy and paste, I select whole columns instead of definite ranges and (at least in that document) I had specifically moved to the end of the sheet range and hidden all those extra cells.
A bit more care and I can stop it reoccurring. Overall, another little MS annoyance!
You can use Ctrl-A to select a range to copy, but I wouldn't use it for selecting a target range for formulas and/or formats. Remember, any cell that you format or enter a formula into will be treated as active, and if you delete the format or formula, the cell stays active. Your active range on the sheet will grow.
Maybe there's something in your macros that caused the file to bulge, but it's really hard to tell.
cheers
teylyn
Microsoft MVP - Excel
At Excelforum, you can say "Thank you!" by clicking the
Select all your cells. Choose Format-Row height, leave it at 12.75, then press OK. Save the file.
So long, and thanks for all the fish.
here it is fixed
just opened in open office then saved as xls
Mojito connoisseur and a dabbler in Cisco
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks