Excessive formatting.
Happens when formatting gets applied to entire rows, entire columns or all cells of a sheet.
In particular the Apple date & ginger and the Apple pear & apricot sheets which have some kind of formatting applied all the way to cell "IV65536" (that's row 65536 column 256) but the last used cells are actually "F22" and "H24".
Need to delete the rows and columns beyond those actually being used.
After running this "Lose That Weight" macro to automatically do this for all sheets in the workbook the saved size was around 33KB.
Code:
Sub LoseThatWeight()
' saved from post #4 of:
' https://www.mrexcel.com/forum/excel-questions/961348-how-get-rid-ghost-rows-without-saving-file.html
Dim x As Long, Lastrow As Long, LastCol As Long
Application.ScreenUpdating = False
On Error Resume Next
For x = 1 To Sheets.Count
With Sheets(x)
Lastrow = .Cells.Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
LastCol = .Cells.Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
.Range(.Cells(1, LastCol + 1), .Cells(Rows.Count, Columns.Count)).Delete
.Range(.Cells(Lastrow + 1, 1), .Cells(Rows.Count, Columns.Count)).Delete
End With
Next x
On Error GoTo 0
Application.ScreenUpdating = True
End Sub
08-30-2020, 06:51 AM
Similar Threads
Export Project in Excel Workbook
Windows 10 
Office 2010
Hybrid Mode