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

[oldpup223]

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.

 

[Jon Peltier]

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.

 

[oldpup223]

no not really as it a much simpler workbook

 

[ACommandLineKindaGuy]

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

 

[oldpup223]

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

 

[oldpup223]

I am sorry but you have lost me as I am not sure what to put into the place of the xXXX

 

[oldpup223]

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

 

[oldpup223]

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

 

[oldpup223]

oops wrong line as it was on
Range(ActiveCell.Row + 1 & ":" & Cells.Rows.Count).Delete