Excel has saved my file in size of 79.1MB or 81,147KB how do I reduce the size

oldpup223

Board Regular
Joined
Jul 31, 2012
Messages
83
I just went from 2010 to 2016 and then to 2019. I 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 it is saving at 81,147kb or 79.1MB and I 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. I have tried everything that I know. I have 14 sheets and a lot of macros with lots of formulas and even upgraded to office 2019 and nothing has helped yet. I even tried to copy and paste to a new sheet and delete the old one on each sheet but that does not help either.
Thank you for any ideas or help you may wish to offer.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Changing Excel versions should have minimal effect on file size (if any). You file(s) are 80 times larger than before. There must be a lot more information in those sheets.
 
Upvote 0
Try this. Put it in a module and then cycle through each sheet making it active and then running the sub

Sub FixUsedRange()

'Delete references beyond a sheet's active used range
'David McRitchie, http://www.mvps.org/dmcritchie/excel/lastcell.htm
'------------------------------------------------------------------
Dim xXXX As Integer
Dim strXXX As String
Dim xlong As Long, clong As Long, rlong As Long

On Error GoTo 0
xXXX = MsgBox("Do you want the activecell to become the lastcell" & Chr(10) & Chr(10) & _
"Press OK to Eliminate all cells beyond " & ActiveCell.Address(0, 0) & Chr(10) & _
"Press CANCEL to leave sheet as it is", vbOKCancel + vbCritical + vbDefaultButton2)
If xXXX = vbCancel Then Exit Sub
strXXX = ActiveCell.Address
Range(ActiveCell.Row + 1 & ":" & Cells.Rows.Count).Delete
xlong = ActiveSheet.UsedRange.Rows.Count
Range(Cells(1, ActiveCell.Column + 1), Cells(Cells.Rows.Count, Cells.Columns.Count)).Delete
Beep
xlong = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Columns.Count
rlong = Cells.SpecialCells(xlLastCell).Row
clong = Cells.SpecialCells(xlLastCell).Column
If rlong <= ActiveCell.Row And clong <= ActiveCell.Column Then Exit Sub
Application.EnableEvents = False
ActiveWorkbook.Save
Application.EnableEvents = True
xlong = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Columns.Count
rlong = Cells.SpecialCells(xlLastCell).Row
clong = Cells.SpecialCells(xlLastCell).Column
If rlong <= ActiveCell.Row And clong <= ActiveCell.Column Then Exit Sub
MsgBox "Sorry, Have failed to make " & strXXX & " your last cell"
End Sub
 
Upvote 0
there is only one array in the entire workbook the rest are just sum= or t= or reference to other sheets for the information it is quite simple I figured it should be about 100kb and the macros are simple also as they only automate the sorting and pasting and arranging of data to other sheets
 
Upvote 0
Damm I did get it to work as dumb as I am and it worked on all but two sheets and there it stopped on ?? well I lost it but it did get my file size down to 543 kb and I can live with that as it now closes and opens fast.
I do appreciate that very much and I do thank you immensely for the education
 
Upvote 0
ok I went back and finally found where it stopped and that is on the
Range(Cells(1, ActiveCell.Column + 1), Cells(Cells.Rows.Count, Cells.Columns.Count)).Delete
not sure why but it does stop there on two sheets
Thank you again
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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