file size

[Qroozn]

i have an excel workbook that is 10.3 MB
i want to reduce this as much as i can.
Is there anywhere in particular i can focus on. where ppl often find there is unneccesary wastage of memory.?

TY

 

[Yogi Anand]

On 2002-03-24 15:31, Qroozn wrote:
i have an excel workbook that is 10.3 MB
i want to reduce this as much as i can.
Is there anywhere in particular i can focus on. where ppl often find there is unneccesary wastage of memory.?

TY

Hi Qroozn:
One of the things you want to do is delete all the rows below your last occupied cell, and delete all the columns to the right of your last occupied cell.
Then save the workbook and reopen it and look for the reduction ... if ythe reduced size is to your satisfaction, you are all set, otherwise you will have to other reductions, perhaps by rearranging your data to occupy the least number of cells, avoid unnecessary formats, ... and so on.

HTH
Please post back if it works for you ... otherwise explain a little further and let us take it from there.

 

[Dave Hawley]

Hi Qroozn

Here is information I keep on reducing file size.

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://support.microsoft.com/search/preview.aspx?scid=kb;en-us;Q299372

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

I hope this helps. Good Luck