Excel is creating very large files

Status
Not open for further replies.

oldpup223

Board Regular
Joined
Jul 31, 2012
Messages
83
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

Well-known Member
Joined
Oct 27, 2005
Messages
17,959
Office Version
2013
Platform
Windows
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.

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

oldpup223

Board Regular
Joined
Jul 31, 2012
Messages
83
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

Board Regular
Joined
Jul 31, 2012
Messages
83
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

Well-known Member
Joined
Oct 27, 2005
Messages
17,959
Office Version
2013
Platform
Windows
You are saving AND closing...then reopening aren't you ??
 

oldpup223

Board Regular
Joined
Jul 31, 2012
Messages
83
yes that is what I have been doing but it doesnt seem to help
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
17,959
Office Version
2013
Platform
Windows
You may have a corrupted workbook
Firstly, another clear method
Code:
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,739
Office Version
365
Platform
Windows
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.
 
Status
Not open for further replies.

Forum statistics

Threads
1,077,850
Messages
5,336,742
Members
399,100
Latest member
darcob

Some videos you may like

This Week's Hot Topics

Top