Excel file big for no apparent reason

[ThomasHOL]

I have a problem that one of my excel files have gotten unusually big and because fo thei also very slow.

I have identified the reason and it seems that 6 different sheets suddenly take up much more space than needed maybe because we are converting it from XLS format to XLSM as a part of coding new macros. The sheet is the primary way of managing the economy of our construction process and has been build up over the last 20 years and contains about 3000 lines of macro code and hundreds of named fields etc. So starting over is not really an option in the short run.

But as I said I have identified the offending sheet and I deleted all the other sheets in the workbook. I then deleted all the macros and finally i had a workbook that is about 2.6 mb in size. I then delete all the data in evet possible way i can think of. I simply select the entire sheet and delete all the data. I also tried the option to clear data and selected all. I checked that there is no conditional formatting or links to other files. All looks empty but the file is still 2.6 mb large

The only thing I can see is that if I do a CTRL+END I end up in the last row of the sheet but there is no way i can change this.

I have uploaded the file on my dropbox and you can find it here BigFileNoReason.xlsm

I hope you have any good suggestions.

 

[RoryA]

Select all the cells, then set the Rowheight to 15, then save the file.

 

[Hillyman]

Go to the last row of your content. Then highlight all the rows before this down to the last row in Excel, i.e., row 1048576, and Delete the rows. Do the same with columns, going across to XFD. Save the file and reopen. The file should be smaller now.

 

[Logit]

Using this on your workbook reduce the size dramatically :

Sub LipoSuction()
'JBeaucaire (8/3/2009)
Dim LR As Long, LC As Long
Dim ws As Worksheet

For Each ws In Worksheets
    LR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row + 1
    LC = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column + 1

    'Clear everything below column A last cell and row 1 last cell
        ws.Range(ws.Cells(1, LC), ws.Cells(ws.Rows.Count, ws.Columns.Count)).Delete
        ws.Range(ws.Cells(LR, 1), ws.Cells(ws.Rows.Count, ws.Columns.Count)).Delete
Next ws

End Sub

USE THE ABOVE ON A COPY OF YOUR WORKBOOK FIRST !!!

IF NO DATA IS LOST, THEN PERFORM ON THE ORIGINAL WORKBOOK.