Excel file with the size 20MB

[jam42a34]

Hi There

Can you please assist me in this case as below:

I created a new excel file with filtering and after i saved the file , i found that the size for this excel file is 19 MB, so is this normal size for an excel file or there is a problem in this file and i have to fix it

Can you please update me back
Thanks and Regards

 

[MPW]

I wanted to thank you Blade Runner for the code you posted. I was just sent a link to it and it reduced a workbook I had this problem with. I did have a question along the same lines.

A while ago I was working on a similar answer but without using the copy/paste function. I was trying to duplicate the entire workbook into a new workbook by trapping the values of each cell, including cell color, formulas, conditional formatting, etc. I finally got stuck on the validation boxes. The reason I have been pursuing this is because of the hidden baggage attached to older workbooks. This baggage is not only in the sheets but even in the modules.

In one case CTRL+Home consistently selected the wrong cell if certain worksheet function was run. We eventually had to go back to an older version that did work and modify it. At that point we stopped using save as to overwrite the existing one and did it in Explorer instead. The problem didn't come back but the other issue is the file size.

My goal was a fresh start for older workbooks, code and all. I do believe it is possible to do it. The reason I was duplicating values instead of copy/paste is that I was not sure whether this might carry the baggage over to the new workbook. My question is, do you think that this is necessary or do you think it is overkill?

Mark

 

[Blade Hunter]

Hi Mark, the way my code works is to simply copy stuff across from sheet to sheet. I have added in the pictures code to show how objects can be added. What I would reccomend is add in another componant for each set of objects you wish to carry across, this way you can pretty much pick and choose which "baggage" goes with the sheet and which is removed.

Let me know if you need help with it.

Cheers

Dan

Edit: Hah just noticed the date, it was 2 years ago today that i wrote that

 

[MPW]

Yeah 2 years in cyberworld. I have been messing with this for a while and I just now found it.

Anyway, the baggage I was talking about is the invisible stuff that Excel holds on to in the background that serves no purpose except to drive up the size of the workbook and cause weird errors.

I liked the way you fixed the formulas after you pasted them in. Would the same method work if you copy the sheets to a new workbook instead of just a new sheet? Sounds like it would be a good way to break the links.

The method I talked about earlier of transferring data to a new workbook reduced a 7mg workbook to under 4mg. It is not doing everything so I am still manually moving some of it but it does look promising .

By chance do you know how to retrieve the parameters of validation boxes or conditional formatting so that I could reproduce them with out using copy/paste?

 

[Dirkasaurus]

Just wanted to say thanks to Blade for providing this routine. Two shortcomings found are that it does not automatically copy the row heights and any code held in the original sheets is not transferred. I found those to be minor, but worth noting. Bottom line is that this saved me a bunch of time and reduced the size of my bloated workbook by 50%. Thanks Blade! You made my day!

 

[Blade Hunter]

Just wanted to say thanks to Blade for providing this routine. Two shortcomings found are that it does not automatically copy the row heights and any code held in the original sheets is not transferred. I found those to be minor, but worth noting. Bottom line is that this saved me a bunch of time and reduced the size of my bloated workbook by 50%. Thanks Blade! You made my day!

Happy to help, I will update the code to transfer row height and col widths, I am sure I can find code on here somewhere to add so i can transfer macros too .

Cheers

Dan

 

[DariaL]

Amazing the stuff I find here when I'm looking for something else!

I'll definitely get use out of this very soon, so thank you for the work that went into it.