Worksheet size is causing a save issue

[Swaroon]

Hi,
I have a workbook with multiple sheets, I ran a macro I found on this site which gives me the size of each tab, per the macro, the 20 tabs add to cc 3.7kb, on saving the file, the file size showing is 30kb.

I would have expected to see one or two tabs with large sizes, usually to do with embedded formatting, clear the rows and save, and the file size reduces, there's no large tabs per the macro result to clear.

The macro within the file is not the most efficiently written but does the job, a lot of .Select statements, the file seems to increase in size after each run and save, any ideas on how to reduce the file size, it's a bit like clearing the cache scenario ?

thanks
Steve

 

[jasonb75]

the file size showing is 30kb.

If anything, I would have said that the file size looks too small. I would expect more than that for a single sheet unless it is almost empty. Very few people would be likely to notice such a minor increase in file size. Unless malicious activity was suspected, I wouldn't even bother investigating the cause.

The code that checks the sheet size might not account for everything. It will be unlikely to include things like the vba project (your macros), the data model (pivot tables) or other similar objects.

 

[Swaroon]

If anything, I would have said that the file size looks too small. I would expect more than that for a single sheet unless it is almost empty. Very few people would be likely to notice such a minor increase in file size. Unless malicious activity was suspected, I wouldn't even bother investigating the cause.

The code that checks the sheet size might not account for everything. It will be unlikely to include things like the vba project (your macros), the data model (pivot tables) or other similar objects.

Hi Jason,

Thanks for the reply, some misinformation on my part, I meant :

Macro shows all tabs add to 4,000kb, and the file saved properties show as 28,879kb

No pivots so maybe it's the vba project as you suggest.

thanks

 

[jasonb75]

That's quite a significant difference compared to what you said earlier. The vba project wouldn't take up that much space.

Best guess without a lot more information would be bloat from inefficient practice / code. There are a number of causes, the biggest being changes to the unused part of the sheet. It might appear empty, but that doesn't mean that it is.

Take a look at the thread that I've linked below, particularly post 7. If you search the forum for 'bloat' or google for 'excel bloat' then you will find many possible causes and suggestions of how to rectify the problems.

Why does formatting a specific range (vs entire columns) result in such a big file size difference?

I've been trying to troubleshoot why my file has crept up in size to 23MB. One of my worksheets I regularly fill with several thousand rows of data (never more than 6,000 rows), and so I formatted each column of cells down to Row 5999. E.g. Range A4 : A5,999 is red font, number to 2-decimals...

www.mrexcel.com

 

[Swaroon]

That's quite a significant difference compared to what you said earlier. The vba project wouldn't take up that much space.

Best guess without a lot more information would be bloat from inefficient practice / code. There are a number of causes, the biggest being changes to the unused part of the sheet. It might appear empty, but that doesn't mean that it is.

Take a look at the thread that I've linked below, particularly post 7. If you search the forum for 'bloat' or google for 'excel bloat' then you will find many possible causes and suggestions of how to rectify the problems.

Why does formatting a specific range (vs entire columns) result in such a big file size difference?

I've been trying to troubleshoot why my file has crept up in size to 23MB. One of my worksheets I regularly fill with several thousand rows of data (never more than 6,000 rows), and so I formatted each column of cells down to Row 5999. E.g. Range A4 : A5,999 is red font, number to 2-decimals...

www.mrexcel.com

Great, appreciate the help, will check them out.