Workbook Growth Excessive

[Alexander]

This workbook with a half dozen worksheets keeps my checking account info.

The largest is the History sheet with 4100 rows, 9 columns, no calculation.

Next is the Current sheet with 700 rows, 9 columns, numerous calculations
including links to a "checks cleared" sheet .

Up until a couple months ago this file increased by about 50K/mo; now it's
increasing by 500K. I've made no significant increases in the rate at which
I've added data.

Just scrolling down in the Current sheet is slow and CPU use jumps to 50%.

I get the same result even with auto-recalculation off.

I've run the Error checking tool, and it didn't find anything.

I've eliminated the History sheet no change.

Now it appears to be growing 100k with the addition of 1 row with a
half-dozen cells.

What's going on?

 

[Pete_UK]

On each sheet do CTRL-End - this will take you to the cell which Excel
thinks is the last one to be used, but this is often incorrect. If it
is way beyond your last column or row, then you should delete all rows
and columns beyond the ones that you are using.

For example, if F is your last column, then click on the identifier
above column G to highlight it, hold down the <shift> key and press
<End> once followed by <Right arrow>, then release <shift>. This will
have highlighted all the unused columns - click on Edit | Delete to
get rid of them. Do the same for the unwanted rows, and then save the
file.

Repeat for all your other sheets. Remember to save the file - you will
not see any improvement unless you do so.

Hope this helps.

Pete

 

[Alexander]

Hi, Pete,

Thanks for the suggestion.

I've checked. All the "last" cells are in the right place.

Alexander

 

[Pete_UK]

Okay, another thing you can try is to get rid of any drawing objects
you might have in the file. For each sheet:

use Edit | Go To... (or press F5), then click on Special | Objects,
press OK, then Delete.

Save the file when you have finished.

Does this improve things?

Pete

 

[Alexander]

Thanks Pete_UK,

GREAT!

GREAT!

That fixed it. (I got up early just to send you this thank you.)

I couldn't find "Delete". I moved the cursor over one of them and did a cut.

How do those get created. I certainly didn't do it on purpose.

Alexander

 

[Pete_UK]

Glad to hear it worked - thanks for feeding back.

There are a number of different shape objects - I don't know how they
find their way into the workbook, but once there they seem to
propagate, as Excel doesn't manage its housekeeping properly.

Incidentally, what did the file size come down to (and from what)?

Pete

 

[Alexander]

Hi, Pete,

The original came down from 3,900Kb to 1,200Kb.

I stripped out the Hist sheet and older tranasction from the original
leaving only `70 rows in the Active sheet. That brought it down to ~2,900Kb.
After the fix 133Kb. That's right, 133Kb.

BTW, after doing the Cut it took forever to close.

Thanks again.

Alexander

 

[Pete_UK]

Well, that's quite some reduction !!

Thanks for feeding back, Alexander.

Pete