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.

---

Originally Posted by iturnrocks

As it turns out, I couldnt wait till tomorrow. I just had a look at it, and it looks great. Its probly a good thing its not a macro, I will most likely be able to figure out what this does. Thanks again for a job well done. I will get a lot of use out of this. I use this kind of thing in most of my worksheets.

-Dan

Hi,

ok on the 'out every 50 or 100 rows for copying', but you can still lose 50 columns on that sheet. ok on the formula fill, you really need to learn that, try http://www.mvps.org/dmcritchie/excel/fillhand.htm - it means that you can write one formula to use on every row in the column, or every column on the row (or both). When you need to hand type every formula you need help.
ok on the password, I just removed it.

To understand the formula, as it would apply to your last block (row 2962)

column BH
=COUNTIF(E$2962:E2962,">0")
sets a number on the row that is the first occurance of that number for the collection, thus 0 1 1 2 2 2 3 4 4 shows the first row blank, the second row had the first action, the 4th, 7th & 8th rows had items.

column BI
=IF(ISERROR(MATCH(ROW()-2961,BH$2962:BH$2970,0)),"",MATCH(ROW()-2961,BH$2962:BH$2970,0))
this uses Row()-2961 (numbers 1 to 9) to find the 1st to 9th item, giving 2 4 7 8 b b b b b (where b = space)

column BK
=IF(ISERROR(MATCH(ROW()-2961,$BH$2962:$BH$2970,0)),"",OFFSET(B$2962,MATCH(ROW()-2961,$BH$2962:$BH$2970,0)-1,0))

column BJ
=IF(ISERROR(MATCH(ROW()-2961,$BH$2962:$BH$2970,0)),"",OFFSET(E$2962,MATCH(ROW()-2961,$BH$2962:$BH$2970,0)-1,0))

column BL
=IF(ISERROR(MATCH(ROW()-2961,$BH$2962:$BH$2970,0)),"",OFFSET(F$2962,MATCH(ROW()-2961,$BH$2962:$BH$2970,0)-1,0))

these 3 columns use a Match on the numbers 1 to 9 on column BH to use that as an offset on columns B E & F to retrieve the required items.

the If(IsError "" to blank unused rows.
the MATCH(ROW()-2961,$BH$2962:$BH$2970,0) is a Match on the numbers 1 to 9 to the fixed range $BH$2962:$BH$2970 (hence the $)

These formula can be applied to the first row of each set but you will need to adjust the $2962 $2970 and the 2961 to be 1 less than the Row number you are using (to produce a 1), the formula can then be filled downwards for that group.

-----------

The remainder of your file I have not yet properly understood.

Normally I would expect an Input similar to your A1:H24 perhaps as a UserForm, then
Some form of Row-per-transaction log of all Invoices, which Row was completed when the Input entries were all made and any errors corrected.

Then a Print form which, by your selecting a row to print, would extract all required details and print them.

You appear to have a Bill of Lading form for each of your 64 possible locations, or a copy of the last 64 Bills printed, either way your file will continue to grow, you will need to review something before your system becomes more unmanageable.

Good luck with this.
---

Originally Posted by iturnrocks

The file is saved as read only, and is used once each week and then starts over as blank. Data entered is never carried over to the next week, and previous weeks are only saved for a few days in that week, so the file always stays the same size.

It is designed to create 30 or so individual BOLs and 1 or 2 Master BOLs. Originally we shipped 2 masters every week with 23 or less orders on each, then we started shipping 1 master with 30+ orders so I created Master 3. Master 2 could possibly eliminated, as Master 3 basically replaces it. As you may have seen Master 1 and 2 contained 2 pages, whereas Master 3 contains 3. Formulas in the page numbers show 1 of 1, 1 of 2- 2of 2, based on whether there is any data on page 2, 3...
Once again, this was built with my limited knowledge of Excel, and I intend to re-write it once I have a better understanding of Excel.
My company has mentioned paying for an Excel class sometime this year since they are impressed with what I have done for them so far. Like I said, these people are wowed with my ability to copy and paste.

Ok - well take the course and make sure they teach you UserForm input and how to save data on a row-by-row basis, you will impress the works further with any ability to immediately re-produce any BOL on screen.

Also, whilst there are always 'better' ways to do things, it's the way that works for you with the least amount of development time that will usually win out. A case in point here is that my formula uses two 'helper' columns as this is easier for me to do, and easier for you to understand. The formula could be written without those, but the development time, and later the 'error tracking' time, make is worthwhile to keep it reasonable simple.
So, if it works for you that's really what counts. You can always improve it later as skills improve or as you pick up other hints on how things are done.

good luck with your course, and your BOL, let us know how you progress with this.

---

Originally Posted by iturnrocks

I did a search and found a sample user form. That is exactly what I need to learn. Thank you very much.

good to see, and for the mail also, and that userform is looking good, the advantage that you have after starting over with your own userform is that you know all that is in there, so when something isn't quite right you will know the what and why.

see also the
With ActiveCell
.Offset(x,y).~~
End With

etc, it will save a lot of typing.

Keep up the good work.
---