Excel is creating very large files

[oldpup223]

I just went from 2010 to 2016 and have some programs that I created a few years back and they are all from 950kb to 1400kb and are all macro enabled worksheets. I just finished a new one in 2016 almost the same thing but with less sheets and macros but now find that is saving at 81,147kb and just cant figure out why the massive difference also this new file takes almost a minute to open or save.
any ideas would be appreciated.

 

[Michael M]

Are there many sheets in the Wb ?
Press CTRL + END and see where the cursor ends up.
If it's waaay down the page then use this code to clean all sheets, then save and close...reopen.

Public Sub MM1()
Dim ws As Worksheet
For Each ws In Worksheets
    ws.Activate
    ActiveSheet.UsedRange
Next ws
End Sub

 

[oldpup223]

I do have 14 sheets in this workbook and four of them when I do ctrl + end the cursor goes down almost 700 rows or to the bottom.
I did try to use your code but either I do not understand how to use or it does not work. Ill bet it is me but I tried to create a macro out of it but cannot get it execute.
How did you plan on me to execute or how do I do that?
Thank you for the help as I do appreciate any help I can get but your quote on the more I learn is quite applicable to me.

 

[oldpup223]

Sorry for not replying last night but had already gone to bed

 

[oldpup223]

I do have the macro working just exactly as you have shown but it still does not do the job but does execute very well

 

[Michael M]

You are saving AND closing...then reopening aren't you ??

 

[oldpup223]

yes that is what I have been doing but it doesnt seem to help

 

[Michael M]

You may have a corrupted workbook
Firstly, another clear method

Sub MM1()
Dim lastRow As Long, ws As Worksheet
For Each ws In Worksheets
ws.Activate
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A" & lastRow + 1, Cells(Rows.Count, Columns.Count)).Clear
Next ws
End Sub

Als, check the size of each sheet to see if one stands out as abnormally large...See the code on this thread, in particular post #7

 

[Michael M]

Duplicate thread here

https://www.mrexcel.com/forum/excel...ize-79-1mb-81-147kb-how-do-i-reduce-size.html

 

[Peter_SSs]

Please do not post the same question multiple times. Questions of a duplicate nature will be locked or deleted, per #12 of the Forum Rules and points 6 & 7 of the Forum Use Guidelines.

In this case I have closed this thread as the other one seems to be getting you close to the mark.

Any bumps, clarifications, or follow-ups should be posted to the other thread.