Need help to reduce file size

Originally Posted by iturnrocks

I still cant figure out how to write macros. If someone could help me out with this I would appreciate it.

Currently I have a worksheet that is 20mb.
This file contains what takes up most of the space. I repeat this line many times throughout the worksheet.
Attachment 7323

Once again it is a zero eliminating code.

If someone could reduce the number of formulas for me, I think I could figure out how to adapt it to repeat throughout the sheet.

Thank you

Just remove the 'If' and in Tools, Options, View untick 'Show Zero Values'

optionally, if you want this for only a specified range, then format the columns required to #

hth
---

Ok, heres the sheet with the results page. This is why I need the spaces removed. The 0 values are not an issue for me. My strung out formulas eliminate the empty cells so that everything is stacked neatly on the BOL.

On the full version, the input page contains 60 copies of what is on the input page, then there is a BOL page for each of these. Then there are entry pages for 3 master BOLs which take info from the individual BOLs. There is an address list page which provides shipping addresses for each BOL.

010807.zip

This spreadsheet only creates BOLs for truck shipments. When I started here, this was done with manual entry of every cell. My spreadsheet has cut about 6 hours off the entry time. Now im looking to reduce the file size so it doesnt take so long to load and save on these antique computers we have at work.


p.s. Brian, you created a similar sheet for me previously-
http://www.excelforum.com/attachment...9&d=1164281726
Im just looking for the basics that will temporarily sort the rows based on whether there is a value in column E, so that Columns BC:BF appear as they do now. I dont want anything to be deleted or hidden, because the forumlas on the BOL sheet are based on the cells in BC12:BF22

Well actually BC12:BF16, but in the full version there is a second page on the BOL which accepts values from BC17:BF22

Originally Posted by iturnrocks

Ok, heres the sheet with the results page. This is why I need the spaces removed. The 0 values are not an issue for me. My strung out formulas eliminate the empty cells so that everything is stacked neatly on the BOL.

On the full version, the input page contains 60 copies of what is on the input page, then there is a BOL page for each of these. Then there are entry pages for 3 master BOLs which take info from the individual BOLs. There is an address list page which provides shipping addresses for each BOL.

Attachment 7334

This spreadsheet only creates BOLs for truck shipments. When I started here, this was done with manual entry of every cell. My spreadsheet has cut about 6 hours off the entry time. Now im looking to reduce the file size so it doesnt take so long to load and save on these antique computers we have at work.


p.s. Brian, you created a similar sheet for me previously-
http://www.excelforum.com/attachment...9&d=1164281726
Im just looking for the basics that will temporarily sort the rows based on whether there is a value in column E, so that Columns BC:BF appear as they do now. I dont want anything to be deleted or hidden, because the forumlas on the BOL sheet are based on the cells in BC12:BF22

Well actually BC12:BF16, but in the full version there is a second page on the BOL which accepts values from BC17:BF22

Hi,

the previous file was not really connected with this, except that it showed that you do not need to multiple-column shuffle the way you do.

In this, columns G H I and K through BF can be replaced by columns BH through BO (ie, replace 51 columns with 8), and no macros were involved.

The formula used will not sideways Fill because of your use of Merge cells, the information in C2:C11 could be stored in E2:E11 and columns C & D deleted (thereby removing the 'merge')
The formula will downwards Fill

The same code works also on your Input sheet on the batches down to that starting at Row 2962, and whilst this would save some 2300 formula cells the impact on your filesize will not be great.

That you have 60 Print sheets with invoices drawn on 124 * 174 cells (ie, 21,576 cells per sheet * 60 sheets = almost 130,000 cells with much merging) will hold the filesize up in the 'higher-than-wanted' range.

But, removal of some columns, and further removal of all the remaining 'If zero " ' tests should help a little.

---