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.
 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,707
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.
 

ACommandLineKindaGuy

Active Member
Joined
May 11, 2002
Messages
304
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

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

Board Regular
Joined
Jul 31, 2012
Messages
83
I am sorry but you have lost me as I am not sure what to put into the place of the xXXX
 

oldpup223

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

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

Board Regular
Joined
Jul 31, 2012
Messages
83
oops wrong line as it was on
Range(ActiveCell.Row + 1 & ":" & Cells.Rows.Count).Delete
 

Forum statistics

Threads
1,078,499
Messages
5,340,740
Members
399,393
Latest member
farlow

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top