Excel file big size

[T8keiteasy]

Hi to all;

Here's a weird behaviour.
I have an excel file which is weitgh 12 Mega !!
I have no macro, about 10 tabs, and no specific things (basics functions only).

If I delete all the tabs except one, the size changes but I still have 2Mega !!!

If i delete all the rows and colums, the size doesn"t change much....

What do you think of that ?
THe file can be found at the given URL...
I wish someone can help, this is really painful!!

Thanks

Now, it's about French users too !!!

 

[Gavona]

And if you delete just the remaining tab?
The file size only resets after you have saved the file.

Gavin

 

[AnotherHiggins]

If all else fails, it is possible that the file is corrupt.

If that's the case, you can try the following to get the data table out of a corrupt file. You'll need to recreate charts, picot tables, etc. But this might help you out:

- Create a new Excel file

- Use a link to get data out of this trouble file (eg ='C:\FilePath\[Book1.xls]Sheet1'!$A$1 )

- Use the fill handle to create links for all data in the data table.

- Copy the cells, Paste Special > Values

- Save the new file

What is the size of this newly created file?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[T8keiteasy]

What I have done is :
Opening the file with open office
Saving the file...

The file haw now correct size...

Thank you bilou



Now, it's about French users too !!!

 

[IrishJim]

Gavona, the file size doesn't always reset just by saving. My co-worker and I have been noticing this on several of our files that are very big. It seems that the "garbage-collection" routines don't always work.
When we see our big files getting over 100M, we right-click on the tab in the spreadsheet, and say copy to a new workbook. Then we close both of them, delete the big one and rename the new one. They've been shrinking to about 24M. It sometimes takes 20-30 minutes, but we need the data!
I came back to Tek-Tips to look for a better way! At least I'm seeing others with the problem.

 

[Gavona]

I did NOT mean that saving the file would on its own reduce file size. Just that the reported and apparent file size and used range only changes after you have saved.

I assume that you have tried all the usual things but here are a couple of vba routines (adapted from Tek-Tips postings)

Sub ExcelDiet()
    Dim ws As Worksheet
    Dim LastRow As Long, LastCol As Long
    Application.ScreenUpdating = False
    On Error Resume Next
    For Each ws In ActiveWorkbook.Worksheets
        With ws
            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(65536, 256)).Delete
            Range(.Cells(LastRow + 1, 1), .Cells(65536, 256)).Delete
            LastRow = .UsedRange.Rows.Count
        End With
    Next ws
     On Error GoTo 0
     Msg = "The last row and column have been reset which should help minimise file size.  Another cause of excessive file size occurs if your spreadsheet has formulae referencing other workbooks.  You can solve this by telling Excel not to save external link values (Tools, Options, Calculation...).  Click ok to let me do this for you"
    ActiveWorkbook.SaveLinkValues = False
    Msg = "If your workbook contains pivot tables then by default Excel saves a copy of the data on which the table is based with the pivot table.  You can reduce file size by disabling this setting (Right click in pivot table, table options, make sure that ""save data with..."" is not ticked.  Next time you open the workbook and modify the pivot table you will have to refresh it first."
    Application.ScreenUpdating = True
End Sub

Private Sub Reset_LastCell()
'Excel Diet works much better than this....I think!

   ' Save the lastcell and start there.
   Set lastcell = Cells.SpecialCells(xlLastCell)
   ' Set the rowstep and column steps so that it can move toward
   ' cell A1.
   rowstep = -1
   colstep = -1
   ' Loop while it can still move.
   While (rowstep + colstep <> 0) And (lastcell.Address <> "$A$1")
      ' Test to see if the current column has any data in any
      ' cells.
      If Application _
            .CountA(Range(Cells(1, lastcell.Column), lastcell)) _
            > 0 Then colstep = 0  'If data then stop the stepping
         ' Test to see if the current row has any data in any cells.
         ' If data exists, stop row stepping.
         If Application _
               .CountA(Range(Cells(lastcell.Row, 1), lastcell)) _
               > 0 Then rowstep = 0
            ' Move the lastcell pointer to a new location.
            Set lastcell = lastcell.Offset(rowstep, colstep)
            ' Update the status bar with the new "actual" last cell
            ' location.
            Application.StatusBar = "Lastcell: " & lastcell.Address
   Wend
   ' Clear and delete the "unused" columns.
   With Range(Cells(1, lastcell.Column + 1), "IV65536")
      Application.StatusBar = "Deleting column range: " & _
         .Address
      .Clear
      .Delete
   End With
   ' Clear and delete the "unused" rows.
   With Rows(lastcell.Row + 1 & ":65536")
      Application.StatusBar = "Deleting Row Range: " & _
         .Address
      .Clear
      .Delete
   End With
   mycount = ActiveSheet.UsedRange.Rows.Count
   ' Select cell A1.
   Range("a1").Select
   ' Reset the status bar to the Microsoft Excel default.
   Application.StatusBar = False
End Sub

Gavin

 

[IrishJim]

Just that the reported and apparent file size and used range only changes after you have saved.
" - I understand and agree. I read too fast.

I hadn't thought of automating the deletion of the "blank" rows and columns. I can't wait to try it out.

Also, it was interesting to see other ways of doing things. I've used other code to get to the end of my data, and now I've got two more ways to try. These seems more robust, for when I don't know the shape of the data range.

I'm hesitant about turning off the "Application.StatusBar". I have some icons I've set up to do macros. Would those disappear?

 

[Gavona]

The status bar is at the bottom of the screen where you see prompts like Ready, Calculate etc. I don't think you can have icons down there - so do not be afraid.

I notice that I did not include in my post the call to the code relating to pivot tables (table options: do not save data with pivot table). For completeness, after the last message box command in ExcelDiet I would add a call to this routine:

Sub PivotData_DoNotSave()
Dim ws As Worksheet
Dim pt As PivotTable
For Each ws In ThisWorkbook.Worksheets
    For Each pt In ws.PivotTables
        pt.SaveData = False
    Next pt
Next ws
Set pt = Nothing
End Sub

Gavin