5.6 MB file, users having difficulties

[beetlebug]

I have a large model over 5MB that appears to have trouble upon saving. It gets hung up at around 40% calculation and is very slow when I do try to Save. The file itself has a few macros in it and the INDEX/MATCH formulas through it's 10+ worsheets. Is going to be a problem with the efficiency of the file no matter what I do?

This tool has to get out to several people to use and it works great! if only they could get it to run quickly (or quicker).

Any thoughts? Would it help to turn off calculation and then let it save (I assume it automatically starts calculating when you hit save as well?)

Thanks,
Rhonda

 

[erik.van.geit]

Surprisingly though, my file is actually much larger in size than it was before (I guess because of the additional sheets I added). I went from about 5 MB to over 10MB

this thing seems corrupt to me from the beginning: you didn't react to my post yet, but you can always try now

 

[beetlebug]

this thing seems corrupt to me from the beginning: you didn't react to my post yet, but you can always try now

hey erik - i actually tried all that stuff you mentioned in your other post, but it didn't help my file much at all (?). The new files that are larger.. were built completely from scratch, so I cannnot believe it is corrupt at this point (?)

Nonetheless, the files are humming along now.. I have got 4 times as much done in just two hours this morning compared with the previous files with the old array formulas. At this rate I will be done this afternoon with a project that took 3 days to do (compared now to 5 hours or so). Works for me!

 

[erik.van.geit]

I can not quite understand why your files are that big
perhaps I missed something?
can you tell again what is in the workbooks to get at that 10Mb?

 

[beetlebug]

I can not quite understand why your files are that big
perhaps I missed something?
can you tell again what is in the workbooks to get at that 10Mb?

Sure...(and the below mentioned file is actuall 11 MB)

Sheets 1-4: about 500 rows, 6 columns each data referenced from the next 4 sheets, with Concatanate formulas on all rows to create a 'label'.

Sheets 5-8: straight data, no formulas (about 500 rows each)

Sheet 9: contains about 100 rows of SUMIF formulas (referencing sheets 1-4), plus straight links to subsequent sheets in this file

Sheets 10 & 11: about 70 rows and 10 columns of straight linked data.

Sheets 12-23: Contains my reports. SUMIFs in 4 columns, for 300 rows; concatanated formulas creating labels (which then go linked to sheets 10 and 11); plus MACROS on each file to 1) Hide all the rows that have zero balances, and a second button/macro to "unhide" those rows (eg show all). I used the filter option "does not equal" 0.

It is necessary for each tab in 12-23 to have its own ability to do the hide row thing because they go to a different person. I can't do a macro for the whole file, hiding rows in all the tabs for that reason.

Could the macros be causing the large file size?

 

[erik.van.geit]

you can do some easy tests to find out
11MB is not normal to my sense: way too much

I can remember that one of my projects was about 3.5MB and it had about 15 sheets, 15.000 VLOOKUP formulas, ten thousands of values, some charts, and really a lot of code, userforms, etcetera

but rebuilding your workbook can be done in less than half an hour
I would go straight that route

best regards,
Erik