File size with macros enabled is huge

Hello,

I'm using Excel 2010

I am new to this website and a complete noob when it comes to excel. I know how to work formulas and such but I know very little about VBA and Macros. Well I was given this challenge of figuring out why a file is so big. The information in the file is quite a bit but not nearly as big as is stated. When the document is saved as an excel workbook (no macros) it is only about 7000 kb but when you save it as a macro enabled workbook it is 166 MB yes MB not KB.

As I understand it there is simply a couple Macro's that enable a pivot chart to be updated/use different info by way of a dropbox and there are charts in a bunch of different sheets that run off of that pivot table.

Does anyone know what could cause the file to be so incredibly massive with one macro (I think it's just one)? Is it saving the workbook as having the sheets that aren't hidden with the hidden sheets and having all of those sheets again in the background with the macro being separately applied to each one?

Is there a different macro that can be used to update/change the pivot table or maybe some other way to update/change the pivot table?

I have attached a screenshot of what I believe to be useful information.

Your help is greatly appreciated. Thanks in advance.

Josh

Originally Posted by JapanDave

Press ctr end on each worksheet and see if the data ends where it should. If it doesn't, then there is data being stored in the cells even though they are blank. You should then delete all the rows and columns that seem to be blank.

Thank you for the tip, but not much luck. There was one sheet that would take me a thousand rows past where it was supposed to so I deleted those thousand rows several times but after every time it would still take me to cell AX2132 even though I had deleted the contents of the cells and the rows themselves multiple times. It doesn't seem to me like a thousand rows would cause the issue but maybe the macro is doing something strange with those 1000 rows?

Hi Josh
If possible, fill your file (7000kB) somewhere on file-sharing, and provide a link to it

Originally Posted by nilem

Hi Josh
If possible, fill your file (7000kB) somewhere on file-sharing, and provide a link to it

I will find out if that is an option. I am an intern so I'm not sure if the data is precious or confidential in any way. I assume that it is not but better safe than sorry.

Nilem, I asked my superior and I am not able to share the document.

So I don't think this is the best solution but I did find a solution. I was in the process of doing a test and the first step was to save the file with a different name so I did that and apparently it was saved as a 2003 compatible version before because when I saved it as a macro-enabled version this time the file size shrank to 7000 kb which is what it should be. So if anyone out there comes across a similar problem try saving the file in the newest version of excel.