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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,020
Messages
5,834,989
Members
430,331
Latest member
Syed Yasir Hannan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top