Excel is creating very large files

Status
Not open for further replies.

oldpup223

Board Regular
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
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
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
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
 

oldpup223

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

Michael M

Well-known Member
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
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.

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Macro to copy values across rows and transposing them and add the user id
    [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Hi,[/COLOR][/SIZE][/FONT] [FONT=Times New...
Top