Takes way to long to save.

**RickABC** · 05-05-2008, 08:16 PM

Hello everyone i am new to this Forum so let me just say thanks in advance for any help I may receive here. I have had a workbook that ive used for work for 2 years now. The file has always only taken a few seconds to save. I exhausted all excel had to offer with formulas and have tried a simple VBA function. That code worked great but it slowed my saves to about 1 min

. So I removed the code and the module but it still takes that long to save. I tried other excel files and just by opening VBE causes my save time to go way up. Makes no sense to me so I thought I would leave it to the experts. I would like to attach the file but its to large to post here (3 MB).

**royUK** · 05-06-2008, 12:56 AM

Large files with many formulas and formatting can be slow to save, just opening the VBE should not affect save time. Does the file use a lot of array formulas? It's difficult to assess without the workbook, you could use a file share site like www.yousendit.com and post the download link. Zip the file first.

**shg** · 05-06-2008, 01:12 AM

I would try CodeCleaner by AppsPro.

I have a 4MB template for pricing that I instance regularly, and it takes about 10s to save. It is loaded with formulas and VBA, but the formatting variations are deliberately modest. What about yours?

**RickABC** · 05-07-2008, 08:56 PM

That is a great idea! I have the file shared at this address:

http://workspace.office.live.com/?id...hhcnRlci5uZXQA

I hope that link works. The file name is called Old_Forms_Master

**RickABC** · 05-08-2008, 06:08 PM

Can someone take a look at the above file?
Thanks

**rylo** · 05-08-2008, 10:27 PM

Hi

Had a bit of a look and was astonished to find 291 sheets.

Just for the heck of it, I started deleting sheets and found save time decreased as the number of sheets decreased.

On a quick look, many of the sheets seem to be in the same format, with just a minor difference. Any chance you could reduce the number of sheets?

rylo

**royUK** · 05-09-2008, 02:32 AM

You could try removing unnecessary formatting, formatted cells add to "bloated workbooks".

**RickABC** · 05-11-2008, 08:22 PM

I cant do much changing because there are many other people using the same file for many years now. I can take a file that is lets say 3 MB that saves in a few seconds. Open up VBE and close it out without making any changes at all and it will add about 15 sec. on to the save. It will also add about .5 MB to the file size. What could be changing or being added to the file just by opening VBE? Can someone else try this on your end to see if you get the same result. It will need to be a large file to see the change. I have Excel 2002.

**RickABC** · 05-15-2008, 09:03 PM

Anyone had a chance to look at my problem? I am in a major bind with this problem. Should I look at maybe paying someone to look in to it?

**royUK** · 05-16-2008, 01:46 AM

Originally Posted by RickABC

I cant do much changing because there are many other people using the same file for many years now. I can take a file that is lets say 3 MB that saves in a few seconds. Open up VBE and close it out without making any changes at all and it will add about 15 sec. on to the save. It will also add about .5 MB to the file size. What could be changing or being added to the file just by opening VBE? Can someone else try this on your end to see if you get the same result. It will need to be a large file to see the change. I have Excel 2002.

Is this happening with every workbook?

**RickABC** · 05-16-2008, 08:35 PM

Originally Posted by royUK

Is this happening with every workbook?

It happens with all of my workbooks that I open up in VBE. If I dont open up VBE then the workbook saves in just a few seconds. I have tryed the files on 3 different computers all with the same "long save time" results.

**royUK** · 05-17-2008, 03:15 AM

If it was on only one machine I would recommend a "install & repair" of Excel, but you say it happens on several computers. Have you checked for any viruses?

**gurusanthanam** · 05-17-2008, 07:15 AM

Try this...i found this on net....
there are code cleaners also in the reducing the size...

There are several things that you could try in order to try and reduce the file size of your workbooks.

1) Delete any empty rows below your data.

Check the "used range" on each worksheet and delete any unnecessary rows, to find the last used row, press (Ctrl + End).

The "used range" is only updated when you close and reopen the workbook though.

2) Save the workbook with the macros disabled, then reopen and enable macros.

3) Export all VBA modules, close and then import them all again.

4) Remove the formats from any blank cells. press (Edit > GoTo) special, select "blank". Then select (Edit > Clear All).

5) Check that you are not saving the workbook as multiple versions (i.e. as "Excel 5.0/95 Workbook (*.xls)" ).

6) Delete any custom number formats which are not being used or are not necessary.

As far i search, deleting empty rows using Ctrl+End...does reduce size...

Regards,
gurusanthanam

**davesexcel** · 05-17-2008, 08:43 AM

Originally Posted by RickABC

I cant do much changing because there are many other people using the same file for many years now. I can take a file that is lets say 3 MB that saves in a few seconds. Open up VBE and close it out without making any changes at all and it will add about 15 sec. on to the save. It will also add about .5 MB to the file size. What could be changing or being added to the file just by opening VBE? Can someone else try this on your end to see if you get the same result. It will need to be a large file to see the change. I have Excel 2002.

I have saved the workbook a couple of times, it takes about 30 seconds, with no changes,
If you hit Ctrl+End on each sheet you will see where excel thinks the end of the sheet is, not where it really is,
some of the sheets end dead on, but other sheets end several rows and columns from the actual end of the sheet, it may not seem like a big deal, but when dealing with 291 sheets, it could make a difference,
Select all the unused rows, goto
edit=>clear=>clear all
then do the same for all the unused columns.
then save the workbook

Here is one site on that topic
http://www.beyondtechnology.com/tips019.shtml

Here's a macro to loop through your Worksheets and selecting the last cell, if it is where it should be then click yes, if the cell selected is way off then click no and fix it,
you may have to copy a similar code as other sheets have different passwords...

Please Login or Register  to view this content.

**royUK** · 05-17-2008, 10:11 AM

I don't think it is a normal case of workbook "bloat". The OP says it is happening with every workbook that he opens on several PCs.

**RickABC** · 05-17-2008, 05:19 PM

Originally Posted by royUK

I don't think it is a normal case of workbook "bloat". The OP says it is happening with every workbook that he opens on several PCs.

Roy you are right because if it was a case of bloat then it would take to long to save even before I enter VBE. I will try to remove the unused rows to see if that makes a difference.

**RickABC** · 05-17-2008, 06:05 PM

Deleting unused rows did not help

**martindwilson** · 05-18-2008, 03:46 AM

ok whats the password to unprotect sheets?

**royUK** · 05-18-2008, 04:02 AM

The password is in the VBE - "flight"

Removing all formatting of non data cells reduces the file size from 3.13mb to 2.66mb

I have also removed all formula but it still closes slowly on saving.

I think it is the heer volume of sheets in the workbook

opening & closing the VBE does not affect the size of the workbook for me

**RickABC** · 05-19-2008, 07:04 PM

Originally Posted by royUK

The password is in the VBE - "flight"

Removing all formatting of non data cells reduces the file size from 3.13mb to 2.66mb

I have also removed all formula but it still closes slowly on saving.

I think it is the heer volume of sheets in the workbook

opening & closing the VBE does not affect the size of the workbook for me

How do I remove formatting of non data cells? I had already opened the workbook up in VBE so you would not notice the increased file size. Thank you guys aor your time.