Excel file with the size 20MB

[jam42a34]

Hi There

Can you please assist me in this case as below:

I created a new excel file with filtering and after i saved the file , i found that the size for this excel file is 19 MB, so is this normal size for an excel file or there is a problem in this file and i have to fix it

Can you please update me back
Thanks and Regards

 

[Blade Hunter]

Re: Excle file with the size 20MB

How much data is in it?

 

[jbeaucaire]

Re: Excle file with the size 20MB

I typically find this is a result of conditional formatting out of control.

Try running this macro on your workbook:

Sub LipoSuction()
'JBeaucaire (6/18/2009)
Dim LR As Long, LC As Long, i As Long
Dim ws as Worksheet

For each ws in Worksheets
    ws.Activate
    LR = ActiveSheet.UsedRange.SpecialCells(xlLastCell).Row
    LC = ActiveSheet.UsedRange.SpecialCells(xlLastCell).Column

    'Clear everything outside of Excel's known "lastcell"
        Range(Cells(1, LC + 1), Cells(Rows.Count, Columns.Count)).Clear
        Range(Cells(LR + 1, 1), Cells(Rows.Count, Columns.Count)).Clear
Next ws

End Sub

Try this on a copy of your sheet, then save the sheet, see if the file size gets better.

 

[jbeaucaire]

Re: Excle file with the size 20MB

Here's a version that assumes the "last cell" trick won't work reliably and sets the used data range by the depth of column A and the width of row 1.

It also tells you what the filesize was before and after running the macro. Remember to run this on a COPY of your sheet because this version has to SAVE the sheet before the message pops up.

Option Explicit

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

fSize = FileLen(ThisWorkbook.Name)

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

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

ThisWorkbook.Save
MsgBox "The Size of the file was " & fSize & " bytes, now the file is " & _
    FileLen(ThisWorkbook.Name) & " bytes."

End Sub

 

[wsjackman]

Re: Excle file with the size 20MB

could this be a 2007 file saved as 2003 compatable?

because of the save method differences, this can cause file bloat for some formula combinations.

 

[jam42a34]

Re: Excle file with the size 20MB

How much data is in it?

Hi Hunter, I hope you are fine and well

The size for this excel file is 20 MB and i found it very big size so if there is any way to reduce thee file size or it is normal that it is in this size.

Thanks and Regards
Abdullah

 

[jam42a34]

Re: Excle file with the size 20MB

Hi My Friend, I hope you are fine and well

The size for this excel file is 20 MB and i found it very big size so if there is any way to reduce thee file size or it is normal that it is in this size.

Can you tell me the step to reduce the size for this file as i didn't understand the macro procedure

Thanks and Regards
Abdullah

 

[Blade Hunter]

Re: Excle file with the size 20MB

What i meant was how many tabs and how many rows / columns of data and formulas?

 

[Sandeep Warrier]

Re: Excle file with the size 20MB

Hi My Friend, I hope you are fine and well

The size for this excel file is 20 MB and i found it very big size so if there is any way to reduce thee file size or it is normal that it is in this size.

Can you tell me the step to reduce the size for this file as i didn't understand the macro procedure

Thanks and Regards
Abdullah

In the Excel workbook press Alt+F11. In the new window that opens up go to Insert->Module and paste the code given by jbeaucaire in the white area.

Then close this new window. In your excel workbook, press Alt+F8. In the window that pops-up select LipoSuction and click on Run.

As jbeaucaire suggested, try this on a copy of your original workbook because generally any changes affected by a macro cannot be undone.

<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[Blade Hunter]

Re: Excle file with the size 20MB

Hi Guys, this didn't work for us in Excel 2007. I have written an alternative, I hope it is useful to someone.

You can change TRMFAT to whatever you want, just make sure you replace it everywhere.

The problem you will find with this code is if your sheet names are as long as the limit. We have not encountered this but you could easily set up a bunch of variables and do it that way.

Sub LipoSuction2()
'Written by Daniel Donoghue 18/8/2009
'The purpose of this code is to offer an alternative to the original Liposuction code written by JBeaucaire for the MrExcel forums www.mrexcel.com
Dim WS As Worksheet
Dim CurrentSheet As String
Dim OldSheet As String
Dim Col As Long
Dim R As Long
Dim BottomrRow As Long
Dim EndCol As Long
For Each WS In Worksheets
    WS.Activate
    'Put the sheets in a variable to make it easy to go back and forth
    CurrentSheet = WS.Name
    'Rename the sheet to its name with TRMFAT at the end
    OldSheet = CurrentSheet & "TRMFAT"
    WS.Name = OldSheet
    'Add a new sheet and call it the original sheets name
    Sheets.Add
    ActiveSheet.Name = CurrentSheet
    Sheets(OldSheet).Activate
    'Find the bottom cell of data on each column and find the further row
    For Col = 1 To Columns.Count 'Find the REAL bottom row
        If Cells(Rows.Count, Col).End(xlUp).Row > BottomRow Then
            BottomRow = Cells(Rows.Count, Col).End(xlUp).Row
        End If
    Next
    'Find the end cell of data on each row that has data and find the furthest one
    For R = 1 To BottomRow 'Find the REAL most right column
        If Cells(R, Columns.Count).End(xlToLeft).Column > EndCol Then
            EndCol = Cells(R, Columns.Count).End(xlToLeft).Column
        End If
    Next
    'Copy the REAL set of data
    Range(Cells(1, 1), Cells(BottomRow, EndCol)).Copy
    Sheets(CurrentSheet).Activate
    'Paste everything
    Range("A1").PasteSpecial xlPasteAll
    'Paste Column Widths
    Range("A1").PasteSpecial xlPasteColumnWidths
    'Reset the variable for the next sheet
    BottomRow = 0
    EndCol = 0
Next
'Excel will automatically replace the sheet references for you on your formulas, the below part puts them back
'This is done with a simple reaplce, replacing TRMFAT with nothing
For Each WS In Worksheets
    WS.Activate
    Cells.Replace "TRMFAT", ""
Next
'Poll through the sheets and delete the original bloated sheets
For Each WS In Worksheets
    If Not Len(Replace(WS.Name, "TRMFAT", "")) = Len(WS.Name) Then
        Application.DisplayAlerts = False
        WS.Delete
        Application.DisplayAlerts = True
    End If
Next
End Sub

This code will basically rename your sheet to have TRMFAT after it and Excel will auto update referencing formulas. It will then create a new sheet with the name of the original sheet and paste the data and column widths in to it before moving on to the next sheet.

Once it has done all sheets it looks through the new sheets and replaces TRMFAT in all the formulas with "", this will point the formulas to the NEW copies of the sheets.

Finally the original sheets are deleted.

Do make sure you test this on a COPY first.

This relies on the last cell and row of data actually having real data. Maybe someone on here can improve on the code to ensure that there really is data in it and not some random unresolved formula.

Cheers

Dan