XL Files growing huge in size

[Guest]

My spreadsheet has a number section that I have built macros to add additional number section for the user if they need more space, and also to delete sections if they added a section but later dont need it. The problem is that if a section is added but then deleted, the file almost doubles in size. I was testing this by adding and deleting a section a few times and the file grew from 412k to 33meg in a short time.
Here is my code any help would be greatly appreciated.

Public Sub AddNum()
On Error GoTo err_AddNum
ActiveSheet.Unprotect ("2468")
Range(Cells(Rows.Count, 3).Address).End(xlUp).Select
[np1].Copy ActiveCell.Offset(2, -2)
ActiveSheet.PageSetup.PrintArea = Parea()
ActiveSheet.Protect ("2468")
Exit Sub
err_AddNum:
ActiveSheet.Protect ("2468")
MsgBox (Err.Description)

End Sub
Public Sub DelNum()
ActiveSheet.Unprotect ("2468")
If Range(Cells(Rows.Count, 3).Address).End(xlUp).Row < 200 Then
MsgBox ("Cannot Delete Last Number Sheet")
Else
Range(Cells(Rows.Count, 3).Address).End(xlUp).Offset(-41, 0).Rows("1:43").EntireRow.Delete Shift:=xlUp
End If
Range(Cells(Rows.Count, 3).Address).End(xlUp).Select
ActiveSheet.PageSetup.PrintArea = Parea()
err_DelNum:
ActiveSheet.Protect ("2468")
End Sub

 

[RaoulF]

Unfortunately I can't answer your query, but I just wanted to add that I had the same experience with a 125 sheet file when I pasted and deleted an object on each page before printing via a simple macro.

The file got to 38Meg and the only solution was to paste each sheet into a new workbook and the file went back to its original size, but I still could not use the print macro.

Hope this helps you salvage the file if nothing else.

 

[gplhl]

Is the workbook shared? If it is goto share Workbook and untick keep history.

 

[Rod Renfro]

No, it is not a shared workbook.
{I forgot to sign in when I posed this question. I posted the first question}
Thanks, this one really has me puzzeled.

On 2002-03-08 16:18, gplhl wrote:
Is the workbook shared? If it is goto share Workbook and untick keep history.

 

[Dave Hawley]

Hi Guys

Unfortunately this isn't that uncommon. It sounds like one of your Worksheets has become corrupt. Here is some information on how these issues can be addressed:

Ways to reduce file size.

1. Save the workbook as one version only. Don't save as multiple versions unless needed.

2. Export ALL modules and Userforms to your Hard drive.

3. Open a NEW Workbook. Window back to the your Workbook and right click on a sheet name tab and select "Move or Copy" then COPY the sheet to the new Workbook. Then save your new Workbook.

4. Do the same for all Worksheets, but each time you COPY a sheet to the new Workbook and save, go to File>Properties and make sure there is not an unusual increase in file size. If there is, then you have probably got a corrupt Worksheet. If so do step 4a

4a. Delete the copied sheet. In the original Workbook, go to the last row of data on the sheet (do not use Edit>Go to-Special>Last cell, see "Find the last Row, Column or Cell" here: http://www.ozgrid.com/VBA/ExcelRanges.htm). Select the entire last row and holding down your Ctrl and Shift key push the Down arrow. This should take you to row 65536. Now go to Edit>Clear>All. Do the same for you Columns, but push the Right arrow! Now Save. Now push Ctrl+A and copy it's content to a New sheet in the Workbook and try again.

5. After you have all Sheets moved open the VBE and Import all your Modules and UserForms.

6. Consider replacing any array formulas with either Pivot Tables or Database functions. Array formulas are notorious for slllloooowwwwing down Excels saving and recalculation.

7 Use Dynamic ranges where applicable as these can often be used to replace references like entire Columns and restrict the referenced range to only what is needed. http://www.ozgrid.com/Excel/DynamicRanges.htm

Here are some interesting links. Some may apply ?

http://support.microsoft.com/support/kb/articles/Q186/3/69.ASP

http://archive.baarns.com/excel/develop/vbaperfm.asp

I hope this helps. Good Luck


_________________
Kind Regards
Dave Hawley
OzGrid Business Applications
Microsoft Excel/VBA Training
If it's Excel, then it's us!
This message was edited by Dave Hawley on 2002-03-08 19:24

 

[Ivan F Moala]

Just to add to all the others good advise

have alook here;
http://support.microsoft.com/search/preview.aspx?scid=kb;en-us;Q299372


Ivan