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.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
You are saving AND closing...then reopening aren't you ??
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Status
Not open for further replies.

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top