Excessive Workbook size - hidden text boxes

grd

Board Regular
Joined
Oct 19, 2005
Messages
210
Hi I have a simple workbook which has been passed to me which is 5MB in size and comprises about 4 pages of printed info over 3 worksheets.

The range of data (simple text and simple formulae) only extends to the visible data, there are no graphics or ranges, no sharing and no formatting (because I deleted it all).

I have created a new workbook by copying and pasting but still 5MB.

By trial and error, I have isolated a small area of 20 rows and 10 columns which create a new workbook of over 1MB. However, when copying one row at a time the size is only 14KB. It is only when I copy the range that the size jumps.

After quite a bit of investigation, it would appear I have a large number of hidden text boxes as a result of someone continually adding them in and then deleting the rows either side thereby leaving an invisible text box with no way of selecting it other than tabbing from a newly created visible text box.

Could anyone suggest a way of deleting all these boxes? I have tried recording a macro (the limit of my macro expertise!) but each box appears to have an individual name and so a general macro will not work.

Any help would be much appreciated.

Regards

Graham
 

grd

Board Regular
Joined
Oct 19, 2005
Messages
210
Wow - quick response.

They are merely text boxes inserted by the user to show some information in the spreadsheet and which he assumed had been deleted during the action of deleting the range of rows above and below.

It was only after extensive testing by me that discovered the problem.

I assume they have been created by using the "text box" icon from the "drawing" toolbar. At least that is what I used to recreate the problem.
 

nbrcrunch

Well-known Member
Joined
Jan 1, 2003
Messages
2,062
this deletes all drawing objects from sheet.

Sub DelAllDraw()
' this one deletes all objects
Worksheets("Sheet1").DrawingObjects().Delete
End Sub
 

grd

Board Regular
Joined
Oct 19, 2005
Messages
210
Thanks nbrcrunch. It works a treat after inserting actual worksheet names.

Is there any way to select all worksheets no matter what their name?
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
This will delete only the TextBoxes:

Code:
Sub Test()
    Dim Sh As Shape
    For Each Sh In ActiveSheet.Shapes
        If Sh.Type = msoTextBox Then
            Sh.Delete
        End If
    Next Sh
End Sub
 

Forum statistics

Threads
1,078,121
Messages
5,338,343
Members
399,228
Latest member
Bucephalas

Some videos you may like

This Week's Hot Topics

Top