• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

diagnosing file bloat

Target

Target

New Member
I have a workbook that continually bloats. It contains about half a dozen sheets, doesn't contain any significant amount of formatting or formula's, or even that much data.

Data is sourced from other books supplied by a 3rd party.

Latest example was 22M... tried all the standard tricks (clear formatting, deleting excess rows/columns, etc) and brought it down to 12.5M (woohoo...)

looking at the underlying xml files I could see 1 sheet had bloated to 136M, so I inserted a new sheet, copied + pasted (values) the data and deleted the original sheet.

workbook size is now 400k

I tried viewing the xml but it either loaded without structure (blech!!), or wouldn't load at all (I tried a number of editors).

so I'm wondering if there is some means of examining the xml to identify the bloat (with a view to preventing it in the first place)
 
If you still have the old file which was bloated. Then do the following
Press F5| Special | Last Cell
check the reference Excel takes you to.

And then check the one on which you did complete replace.

Are they different?
 
shrivallabha said:
If you still have the old file which was bloated. Then do the following
Press F5| Special | Last Cell
check the reference Excel takes you to.

And then check the one on which you did complete replace.

Are they different?
Click to expand...

yes they are, which is obviously why that sheet is bloated

what I don't understand is why this isn't reset by deleting all rows/columns outside the used area AND using clear all to remove anything potentially outside the necessary range.

on an aside it occurred to me that I could simply use a text compare tool to look at the 2 xml files but turns out that's a bust as well
 
You could try following with the bloated file.
  • Locate last cell using xlLastCell method as above.
  • Delete the rows upto last unfilled row. Make sure you select Entire Row and Delete and not clear contents.
  • Repeat the same for columns.
  • After doing this, save workbook and close it.
  • Reopen the workbook and verify that the xlLastCell is now pointing to correct cell.
If yes, check the file size.
 
I'd already done that (more than once) and it made no difference

Ultimately it's a moot point I suppose as I resolved it by copying the data to a new sheet, but I guess I'd like to know the reason so I could avoid it in the future

many thanks for your help though!!
 
You must log in or register to reply here.
Back
Top