REBUILD your workbook

[erik.van.geit]

Hi, guys and gals,

It is my habit to REBUILD the projects which are finished.

PURPOSE:
decrease filesize
increase speed
avoid some strange behaviour of all kind

WHAT are we talking about ?
During the development of a workbook+project you are creating, deleting, moving all kinds of items: formats, cells, controls, shapes, modules, variables, .... Some garbage is staying in "the background" of your file. It's like Excel remembers things which are useless for you: f.i. variables which doesn't exist anymore. It's like there are knots in the formulareferences.

QUESTION
One of my weak points is finding my way on websites. Perhaps there does already exist some tips on rebuilding workbooks. Can you provide links? Also your comments are welcome: if I'm wrong (exaggerations or concessions) feel free to share your experiences.

HOW TO REBUILD WORKBOOK
1. create NEW workbook
during the process:
a. save often & use timestamped filenames
b. avoid deleting & cutting cells, shapes, controls, ... use Ctrl-Z
c. prefer to go back a version and rebuild instead of delete or cut

2. sheets
rebuild from scratch
copy only range which is really used
a. drastic: old to new sheet: only pastevalues allowed, enter formats etcetera manually (or using some code) (copy WITHIN new sheet allowed of course !)
b. quicker: paste used range

3. forms
rebuild from scratch
you can copy controls, Ctrl-Select is OK, but don't use shift-select (to avoid copying "invisible" controls: I've seen often more controls on a form than intended)

4. modules
don't copy modules
a. copy text of all modules to textfiles
b. save and close the textfiles
c. delete modules
d. create new modules and paste text from textfiles
(it can be good to rebuild your sheets from scratch also)

thank you for reading!
I hope this thread will be useful
Erik

 

[kkisebach]

You know what's a surprisingly obvious and effective way to reduce file size sometimes?

Go 1 row down from the last useful row in each worksheet.
Highlight that row and everything below (Ctrl-Shift-Down). Delete it all.

I got a 6MB file down to 630 kB this way, after goofing around with the ZIP/xml looking for the problem. Once I saw one worksheet far bigger than expected, I tried this and VOILA... did it to all of the other ones. I had a lot of Data Validation and formatting there for no reason. It's an XML file, so I suppose it has to have a record of every aspect of every cell that is anything but blank and unformatted.