size of Excel workbook

ggeoff · #1 08-30-2020, 02:58 AM

Hi

I have revised a workbook and now find that its size has gone from 50KB to 82MB. The workbook is available from: local food

NoSparks · #2 08-30-2020, 06:51 AM

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

ggeoff · #3 08-30-2020, 10:15 AM

Thanks

I have never used macros. I dont know how to delete what appear to be empty cells.

NoSparks · #4 08-30-2020, 11:24 AM

You don't have to use the macro, you can do it manually.

Here's your file already done

ggeoff · #5 08-31-2020, 03:19 AM

Thanks. How could I have doe it manually, in case I make the same mistake in future? Thanks once again for sorting this issue

Purfleet · #6 08-31-2020, 04:29 AM

You just need to select the unused columns > right click delete and then unused rows > right click delete

for example on the 'Apple date & ginger chutney' worksheet select column H, press ctrl & shift, right arrow (all columns will be selected) then right click and delete. For rows select row 23, ctrl & shift, down arrow (all rows will be selected) then right click delete.

That is all the macro is doing, but all all sheets, much faster than we can so manually

ggeoff · #7 08-31-2020, 07:25 AM

Thanks Purfleet, I have done that to all the worksheets but the workbook size is still 82MB. I picked on a cell at random and found that on the Format menu the cell was locked, perhaps I should go through all sheets and unlock selected cells?

Purfleet · #8 08-31-2020, 08:33 AM

Shouldnt need to. Just select the Apple date & ginger chutney sheet select H (so the whole column is selected) and ctrl shift right arrow, right click and delete. I just did it and saved and it was reduced to 42mb

ggeoff · #9 08-31-2020, 09:46 AM

I cant understand it. I get no resulting reduction in size but I am getting a flashing message along side a white Sign in box- that has appeared recently- the message is "Not Responding".

Purfleet · #10 08-31-2020, 09:58 AM

That sounds like a resource issue, but you are running win 10 and excel 2016 (according to your profile) so i assume you have a relatively decent PC

Are you doing the update in excel online or from the server version of the file?
Trying opening the file from your PC (it takes about 15 seconds to open on my PC and then another 15 to enable editing) then try the steps

ggeoff · #11 08-31-2020, 10:27 AM

I have a Dell 690 and 2 TB hardrives C: drive is an SSD and one of the hard drives is in a mirror (2x2TB) Intel Xeon E5345. Internet slow but the workbook is on my PC. Windows 10 up to date. NoSparks kindly cleaned the workbook fo me so I can use that to replace the one on the website. I am using Windows Defender which is up to date.

Purfleet · #12 08-31-2020, 11:05 AM

Yeah, so decent enough pc.

I dont have any problems cleaning the file, so i would just make sure it in the local file. maybe re-dlownload it and do the clean up on that file?

NoSparks · #13 08-31-2020, 11:41 AM

When clicking on a cell and looking at its Protection under format cells...
default setting is locked... but is only locked IF the sheet is protected.

When you delete the rows and columns as Purfleet has described the size won't reduce until Excel re-establishes the used range of the sheets which it does upon closing and saving the file.

ggeoff · #14 09-01-2020, 07:53 AM

Hi NoSparks I was running another Excel workbook at the same time so I do not think that helped. Closing all Excell workbooks and restarting and then going through the delete unused rows and columns and saving and closing still doesn't reduce the workbook size. The file is being saved as i set up a test workbook and deleted one of the spreadsheets after anther and the file size started to reduce, particularly with the deletion of Tomato and Apples. The size of the spreadsheets are no more than 3K each and then a massive drop to 41MB.

NoSparks · #15 09-01-2020, 10:23 AM

Sorry Geoff, I have no idea as to how or what you're doing.

Here's what I did:

Downloaded the file you linked to in post 1, (82.4 MB) to a folder on my hard drive.

Starting with no workbooks open, opened your file (took 62.6 seconds)

hit Alt + F11 to bring up the VBA environment
clicked Insert and selected Module
pasted the macro of post 2 into the module.

Went back to the Spreadsheet.
hit Alt + F8 to bring up the Macro dialogue
Selected the only macro available "LoseThatWeight" and clicked Run.

Things ground away for over a minute then came back to life.

Clicked the X in top right corner to close the workbook
was asked if wanted to make changes to 'chutney-calculator.xlsx
selected save
get a pop-up saying VB project can't be saved in a macro-free workbook...
To continue saving as a macro-free workbook, click Yes
I clicked Yes and Excel saved the workbook and closed it.

Looking in the folder where the workbook was downloaded to its size is now 33 KB and it loads in a second.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How do I copy a workbook to another workbook and keep the formatting within the new workbook	excelforsue	Excel	3	09-15-2020 03:37 AM
Printing orientation - Excel Workbook	amason	Excel	3	05-14-2017 10:10 PM
Export Project in Excel Workbook	codeghi	Project	3	09-28-2016 11:32 AM
How to add attached excel workbook with hyperlink in the same workbook	lynchbro	Excel Programming	0	02-24-2015 01:29 PM
Shared Excel workbook	eguru2	Excel	0	07-12-2011 12:02 AM