MrExcel.com - Your One Stop for Excel Tips & Solutions

Excel grows in size after using [Fill Color]

Posted by Danny Robinson on May 05, 2001 12:36 AM

My XLS has grown from 1Mb to over 8Mb after selecting
[No Fill] from [Fill Color] on Formatting ToolBox
on a range below used cells to the end of the sheet.
I've worked on the 30 sheets within one XLS and I want
to "clean" out unwanted formatting and then re-apply
only the background fill colors where they're needed.
Is there a macro that 'copies' sheets to a new sheet
without copying all of the formatting, or better,
copies everything except the fill color I've used.
I've tried 'Save As' but it still is too large.

Posted by Dave Hawley on May 05, 2001 2:39 AM


Hi Danny

I would say you have a corrupt Workbook or Sheet, try these steps of mine to reduce corrupt files and sheets.


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 Worsheets, 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 probaly got a corrupt Worksheet. If so delete the sheet and go back to the Workbook it came from. Select it and push Ctrl+A and copy it's content to a new sheet. Then 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.

Here are some intresting links. Some may apply ?

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

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


By following these steps I have succeeded in reducing a Workbook by 75%I hope this helps. Good Luck


Dave


OzGrid Business Applications