Help Requested to Shrink File Size

[lovecolorado]

I'm creating a spreadsheet to analyze the membership data for a chapter of a professional society. The spreadsheet's analysis and presentation is coming along well, but I'm confused as to why the file size has grown so large and suspect that at least some of the slow execution comes from this size. (If you change any character on the first tab, a flag will be set to recalculate the 'Output - Reports' tab. If you click on the 'Output - Reports' tab, the spreadsheet will take over a minute to complete its calculations.)

I am 'used to' large file sizes coming from graphics that are attached to the file in one way or another, but there are no graphics files in this spreadsheet. With a few characters of fake membership data in only 150 cells, the file is nearly 2 MB in Excel's binary form and over 3 MB in Excel's standard format. My purpose in posting the file is to see if anyone can tell me why the Excel file has grown so large and if there is anything I can do to shrink the size.

I can reduce the file's execution time by not using conditional formatting to establish colors that I then use to sort; however, I have not made this change because the time required to hide unused rows of output is so large.

[Mumps1]

Conditional formatting and formula calculations will slow the macro down. You have extra unused rows and columns that can be deleted to help shrink the file size. Go to each sheet, hold down the CTRL key and press the END key. This will take you to the last recognized cell. From there you will be able to tell how many blank rows and columns you have that can be deleted. In the "Instructions and Scratch Pad" you have thousands of rows with formulas that return no values. I deleted the rows starting at row 201 and that alone reduced the file to 232 kb. After trying some of these strategies, save the file and re-open it.

[lovecolorado]

The rows are blank because the spreadsheet is populated only with a few rows of fake data. Real chapters might have 5000 rows of data, and might have 100 rows of data. Since I want the spreadsheet to be usable by chapters of all sizes, I have thousands of rows with formulas that may, or may not, return values - depending on the chapter size. So, just reducing the rows with formulas that do not return values is not an option to significantly shrink the file size.

It has occurred to me that the file's size might, predominantly, be due to these formulas, and I could completely likely shrink the file dramatically by re-architecting the spreadsheet: Instead of having thousands of formulas (mostly array formulas) waiting on the possibility they will be populated, I could move all of those formulas into VBA code. That would shrink the size of the file (possibly significantly) and the VBA code could then populate as many (or few) blank rows as the chapter has data. Does anyone know how this would effect the spreadsheet's performance? I suspect the array commands are far faster than anything I could program, but I haven't tried this before - so I'm asking the community for their input.

[6StringJazzer]

First, a 2MB file is not unusually large. I have a 25MB that is pretty nimble.

With a few characters of fake membership data in only 150 cells

I don't know which sheet you are referring to there, but although there is data in only a relatively few cells, the used ranges on two of the sheets are very large.

On sheet Instructions and Scratch Pad the used range extends to CJ7180, and there are formulas in every row and column. When I isolate this sheet, I get a file that is 910KB.

On sheet Input - Membership CSV File, your data is only 8 rows but the used range extends to over 7000 rows. When I isolate this sheet, the file size is 933KB, and when I delete the unused rows, it becomes 58KB.

As to why your code is slow, I'm not sure. The only loop I could find is limited to rows containing data, which is 16 in your sample. However, the code encountered a runtime error in the sort.
runtimeerror.JPG
badsort.JPG

[lovecolorado]

First: My apology that the file didn't execute properly. I can fix that, but posting a fixed file may not be needed because of the next point:

Second:

When I isolate this sheet, the file size is 933KB, and when I delete the unused rows, it becomes 58KB

This is the type of analysis I was hoping someone would show me how to do. Thank you.
This seems to show the file's size is due entirely to the thousands of formulas, which answers half of my original question: Why is the file so large?

The second half of my question is "What can I do instead?" and I proposed one answer in my previous post: Move all of the formulas into VBA code. The question then is: Can anyone predict how this will impact my speed?

[Mumps1]

I would think that if you replaced the formulas with code, it would improve the speed because the code would deal only with the actual data rather than with all the rows with formulas.

[lovecolorado]

Interesting opinion about why, for this file, the VBA code might execute more quickly than Excel's array formulas. It makes enough sense that I'll try it.

The array formulas are sufficiently repetitive that replacing them with a well-written VBA module with arguments would not be too difficult. I didn't start with them because, when I first looked for ways to slice and dice a potentially very large CVS file, array formulas were the first thing I found.

Thanks all! I'll go try it.

[Mumps1]

Good luck with it. Let us know how it works out.