Bloated Excel Workbook (2000)

Topic

New Reply

I keep having a problem with Excel workbooks suddenly shooting up from perhaps 250kb to 6-8MB. It has 5 worksheets, with a max of 200 rows in each. I’ve checked to make sure the last cell is not below the actual data. In fact, I made a copy of the workbook, selected an entire worksheet, used Edit, Clear, All, for each worksheet, but the file barely changed. Then I deleted the first worksheet, and it decreased by 75%! Deleting each empty worksheet moved the size down more until it was at 20KB with just one sheet.

I hope that someone can tell me what is causing this.

Thanks

Reply | Quote

Replies

Hi,

This sort of thing can happen if you’ve got any formatting, values (inluding nulls) outside the ‘used’ range, and/or references to cells outside the ‘used’ range.

Another trap can be if you’ve had an embedded object attached to a cell in a row/column that you’ve deleted. Sometimes that simply reduces the object’s height or width to 0, so it can’t be seen – but it’s still in there.

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Thanks Macropod… is there a way to detect such objects?

Reply | Quote

Other than hoping for a eureka moment when moving the cursor about the screen (it’s shape will change as it passes over an embedded object), the only reliable way I know of would be via a macro. For example:

Sub FindShapes()
If ActiveWorkbook.ActiveSheet.Shapes.Count > 0 Then
For Each Shp In ActiveWorkbook.ActiveSheet.Shapes
ShapeNames = ShapeNames & Shp.Name & vbCr
Next Shp
MsgBox “Found Shapes:” & vbCr & ShapeNames
End If
End Sub

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

macropod — I did find that there are several ‘Pictires’… now I need to find out how to find them in the spreadsheet. Thanks!

Reply | Quote

You can find out where the n-th shape is by typing the following instruction in the Immediate window, then pressing Enter:

? ActiveSheet.Shapes(n).TopLeftCell.Address

(replace n by a number in the range of 1 to the number of shapes). Or, to see all the top left cell for all pictures, use this variation on macropod’s macro:

Sub FindShapes()
Dim ShapePos As String, Shp As Shape
If ActiveWorkbook.ActiveSheet.Shapes.Count > 0 Then
For Each Shp In ActiveWorkbook.ActiveSheet.Shapes
ShapePos = ShapePos & vbCr & Shp.TopLeftCell.Address
Next Shp
MsgBox “Shape Top Left Cells:” & ShapePos
End If
End Sub

Reply | Quote

You can find out where the n-th shape is by typing the following instruction in the Immediate window, then pressing Enter:

? ActiveSheet.Shapes(n).TopLeftCell.Address

(replace n by a number in the range of 1 to the number of shapes). Or, to see all the top left cell for all pictures, use this variation on macropod’s macro:

Sub FindShapes()
Dim ShapePos As String, Shp As Shape
If ActiveWorkbook.ActiveSheet.Shapes.Count > 0 Then
For Each Shp In ActiveWorkbook.ActiveSheet.Shapes
ShapePos = ShapePos & vbCr & Shp.TopLeftCell.Address
Next Shp
MsgBox “Shape Top Left Cells:” & ShapePos
End If
End Sub

Reply | Quote

Hi,

You can find out what cells the shapes are attached to via Hans’ reply, and/or, you can reveal them via:

Sub ResizeShapes()
If ActiveWorkbook.ActiveSheet.Shapes.Count > 0 Then
    For Each Shp In ActiveWorkbook.ActiveSheet.Shapes
        Shp.Select
        ' The next with statement resizes all objects to an arbitrary value.
        With Selection.ShapeRange
            .Height = 100
            .Width = 100
        End With
        On Error Resume Next
        ' The next with statement resizes picture and OLE objects to their full size.
        With Selection.ShapeRange
            .ScaleHeight 1, True
            .ScaleWidth 1, True
        End With
    Next Shp
End If
End Sub

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

I am trying to use this code to resize a control that is hidden on a worksheet (so I can locate it and kill it). The code that Hans provided shows the position of this control as $E$7 (although I cannot see it). When I run this code to try to resize it, I get an error with Shp.select (method of object ‘shape’ failed). I did insert a Dim statement to dim shp as shape. Any idea why the code is not running?

Reply | Quote

You cannot select a hidden object.
You can delete it even if it is not visible.

activesheet.shapes(1).delete

Or to make it visible:

activesheet.shapes(1).visible = true

Set the shape number as appropriate.

Steve

Reply | Quote

You cannot select a hidden object.
You can delete it even if it is not visible.

activesheet.shapes(1).delete

Or to make it visible:

activesheet.shapes(1).visible = true

Set the shape number as appropriate.

Steve

Reply | Quote

I am trying to use this code to resize a control that is hidden on a worksheet (so I can locate it and kill it). The code that Hans provided shows the position of this control as $E$7 (although I cannot see it). When I run this code to try to resize it, I get an error with Shp.select (method of object ‘shape’ failed). I did insert a Dim statement to dim shp as shape. Any idea why the code is not running?

Reply | Quote

Hi,

You can find out what cells the shapes are attached to via Hans’ reply, and/or, you can reveal them via:

Sub ResizeShapes()
If ActiveWorkbook.ActiveSheet.Shapes.Count > 0 Then
    For Each Shp In ActiveWorkbook.ActiveSheet.Shapes
        Shp.Select
        ' The next with statement resizes all objects to an arbitrary value.
        With Selection.ShapeRange
            .Height = 100
            .Width = 100
        End With
        On Error Resume Next
        ' The next with statement resizes picture and OLE objects to their full size.
        With Selection.ShapeRange
            .ScaleHeight 1, True
            .ScaleWidth 1, True
        End With
    Next Shp
End If
End Sub

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

macropod — I did find that there are several ‘Pictires’… now I need to find out how to find them in the spreadsheet. Thanks!

Reply | Quote

Other than hoping for a eureka moment when moving the cursor about the screen (it’s shape will change as it passes over an embedded object), the only reliable way I know of would be via a macro. For example:

Sub FindShapes()
If ActiveWorkbook.ActiveSheet.Shapes.Count > 0 Then
For Each Shp In ActiveWorkbook.ActiveSheet.Shapes
ShapeNames = ShapeNames & Shp.Name & vbCr
Next Shp
MsgBox “Found Shapes:” & vbCr & ShapeNames
End If
End Sub

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Thanks Macropod… is there a way to detect such objects?

Reply | Quote

Hi,

This sort of thing can happen if you’ve got any formatting, values (inluding nulls) outside the ‘used’ range, and/or references to cells outside the ‘used’ range.

Another trap can be if you’ve had an embedded object attached to a cell in a row/column that you’ve deleted. Sometimes that simply reduces the object’s height or width to 0, so it can’t be seen – but it’s still in there.

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Edit, clear all does NOT reset the selected area. You can only do that using two methods:

– DELETE the entire rows/columns
– use this code:

Dim oRange as range
Set oRange=Activesheet.Usedrange
Set oRange=Nothing

To get rid of all objects, press F5 (goto), click special, check “Objects”. OK your way out and hit the Del key.

Reply | Quote

macropod, hans, jan:

I want to thank all three of you for your valuable information. Since there were no objects that I intentionally added to my worksheet, I tried Jan’s suggestion, using the GoTo/Special/Objects. That selected all of the objects and allowed me to eliminate them with one keypress of the delete key!

I will keep the VB code as a sample for future reference. Is there an comprehensive reference that lists all of the methods that can be employed in Excel VB?

This is definitely a lesson that I will remember, and probably have the opportunity to share with my peers.

Thanks again!

Reply | Quote

macropod, hans, jan:

I want to thank all three of you for your valuable information. Since there were no objects that I intentionally added to my worksheet, I tried Jan’s suggestion, using the GoTo/Special/Objects. That selected all of the objects and allowed me to eliminate them with one keypress of the delete key!

I will keep the VB code as a sample for future reference. Is there an comprehensive reference that lists all of the methods that can be employed in Excel VB?

This is definitely a lesson that I will remember, and probably have the opportunity to share with my peers.

Thanks again!

Reply | Quote

Edit, clear all does NOT reset the selected area. You can only do that using two methods:

– DELETE the entire rows/columns
– use this code:

Dim oRange as range
Set oRange=Activesheet.Usedrange
Set oRange=Nothing

To get rid of all objects, press F5 (goto), click special, check “Objects”. OK your way out and hit the Del key.

Reply | Quote