excel file size increasing incorrectly

NoSparks · #8 09-09-2020, 04:02 PM

Running this macro against the file the size was reduced from 5,564 KB to 345 KB
The sheets didn't seem too bad except for the Emails sheet where the used range got reduced from last cell being M1048756 to H113.
That's 13,633,828 cells down to 904, and there's still boarder formatting in column F running down to the end of the column.

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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Word file with a lot of pictures. Picture size small, File size HUGE	Blackcloud_9	Drawing and Graphics	1	04-10-2018 06:04 AM
Excel file size in OneNote 2016	hirod	OneNote	0	02-17-2018 01:09 AM
Permanently increasing type size in Outlook	417144	Outlook	2	05-01-2016 09:03 AM
Permanently increasing type size in Outlook	417144	Outlook	1	05-01-2016 12:41 AM
Copying excel charts into word - huge file size.	Matt.M	Word	2	04-02-2014 07:38 PM