Ways to reduce file size

[MrMajinbuu]

HI,

I have constantly run in errors like " Too many different cell formats", on top of that it takes long to open and save file. Current file size is 36MB.
Can anyone please advise which of the following actions take up large file size and/or slow down spreadsheet? and any recommended fix?

My workbook contains:

1. snippets within spreadsheet
2. vlookup and direct cell reference to data link to external workbook
3. sumifs data within same workbook (120,000 rows)
4. sumifs data on external workbook (120,000 rows)
5. various shape and difference color and font formatting
6. Hyperlink within same workbook

Thanks in advance

 

[Alex Blakenburg]

Yes that was normal. I didn't expect a big size reduction from that. If you hit a custom style count of over the 65k mark the file typically becomes corrupt and unretrievable. ( I expect the exact number is the old xls row limit)
It only removes styles (formatting), so you would only need to see if there is anything visually you don't like the looks of but I have never found anything myself. It does not impact how anything in the spreadsheet functions.

Save a copy of the workbook at that point. Then on a copy of the workbook try @MARK858 post # 9.
We can't see your data but if you have prepopulated rows "below your data" with formulas that return "" until a data cell has been filled then
I would swap out the 2 xlValues for xlFormulas which makes it a little less aggressive. However that could be part of the problem and you have formulas way past where you will ever populate data adding bulk.

Speed issues especially related to opening the spreadsheet could well be related to external connections. It can also add to the size since it caches information from the external source. But start with Mark's code.

 

[MrMajinbuu]

Yes that was normal. I didn't expect a big size reduction from that. If you hit a custom style count of over the 65k mark the file typically becomes corrupt and unretrievable. ( I expect the exact number is the old xls row limit)
It only removes styles (formatting), so you would only need to see if there is anything visually you don't like the looks of but I have never found anything myself. It does not impact how anything in the spreadsheet functions.

Save a copy of the workbook at that point. Then on a copy of the workbook try @MARK858 post # 9.
We can't see your data but if you have prepopulated rows "below your data" with formulas that return "" until a data cell has been filled then
I would swap out the 2 xlValues for xlFormulas which makes it a little less aggressive. However that could be part of the problem and you have formulas way past where you will ever populate data adding bulk.

Speed issues especially related to opening the spreadsheet could well be related to external connections. It can also add to the size since it caches information from the external source. But start with Mark's code.

Thanks for that. One last silly question, as i am new to VB and don't want to mess up my workbook. What should I do when prompt with question " The following features cannot be saved in macro-free workbooks (see snippet)?

I am planning to re-use the script and to run on other workbooks, I presume Microsoft is clever enough to allow user to recall a file and re-run it without having to key in the script. What are the options?

Thanks for sharing knowledge.
Appreciated

 

[Alex Blakenburg]

If you add VBA to a workbook with the xlsx extension then you will get that message. A workbook with VBA needs to be saved as a Macro Enabled workbook ".xlsm" or a Binary Workbook ".xlsb" if you save it as xlsx it will remove the VBA on saving.

If the macro is specific to the workbook then we generally save the workbook as Macro Enabled.
If as you are suggesting you want to save it as a utility that you can use on other workbooks the most common practice is to save it in what is called a Personal Workbook. Below is a link to a 3 minute Mr Excel (Bill Jelen) video on it.
Note: In a Personal Workbook you generally want to run the macro on the ActiveWorkbook and or ActiveSheet, so make sure the macro does not explicitly reference a workbook or a sheet and does not use the term ThisWorkbook. (my macro used ActiveWorkbook)