Unsure how my Macros have made my WorkBook over 20MB?

[donnabee]

Workbook Here - WARNING it is 20MB plus

I have been (attempting to) clean up my workbook this morning, it is really large and takes a while to open, save and then close.
I found some "solutions" online and tried them (compiling VBA, disabling macros, enabling again, deleting formatting not required etc)
It's not really an option to split the worksheets in the book and create another book.

The workbook takes a while to open, save and close again.

Have I done something weird with the Macros? Do I have things where they should not be?

 

[donnabee]

I can't edit the post - 40 MB not 20.

 

[Michael M]

If you do a CTRL + End on each sheet where does the cursor go to ??
If it's waaaay down the sheet, you will need to clean the sheets up as they have file bloat.
You cold try running this macro...Save the file..reopen

Sub MM1()
    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("*", , xlValues, , xlByRows, xlPrevious).Row
            LastCol = .Cells.Find("*", , xlValues, , xlByColumns, 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

 

[donnabee]

If you do a CTRL + End on each sheet where does the cursor go to ??
If it's waaaay down the sheet, you will need to clean the sheets up as they have file bloat.
You cold try running this macro...Save the file..reopen

Sub MM1()
    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("*", , xlValues, , xlByRows, xlPrevious).Row
            LastCol = .Cells.Find("*", , xlValues, , xlByColumns, 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

Thank you so much!!!!

 

[Michael M]

Did it help ??

 

[Michael M]

without seeing any of the code, you may be applying formulas, formatting etc, over entire columns and rows, rather than only the areas that need to be affected.
Maybe posting some of the macros would also help.

 

[donnabee]

I can't tell what the Macro you gave me did, it seems about the same regarding time to close and open etc.

I linked the workbook because I have 4 macros and conditional formatting in the workbook.
I'm not sure if I have put the macros in properly, or if I have made other mistakes.

 

[Michael M]

It should have reduced the file size !
Why don't you post the macros on here, and we'll take a look to see if they are causing the bloat.

 

[Michael M]

You have formulas on every sheet that go down to rows 22000.....yet you are only using about 250 rows.
That wouldn't help your processing. You are also using a concat formula in column "N", simply to add a pipe "|".
Why not do a find .jpg and replace with .jpg| in column "M" then column "N" isn't needed.

 

[Michael M]

Further, delete the "BLANK" sheet, it is using over 1 million rows of formulas. If you need the blank sheet as a reference, reduce it to a couple of rows.
It looks a Mtce management workbook to me.