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,081,691
Messages
5,360,647
Members
400,592
Latest member
katekoz

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top