Object Selection

  • Thread starter Thread starter Tony7659
  • Start date Start date
T

Tony7659

I have found that for some reason one of the tabs on my spreadsheet contains
thousands of blank "Text Boxes". The text boxes do not have borders so they
are invisible thus causing a slowdown in that particular area of the
spreadsheet tab. Is there a way to select them all and delete them in one
shot? or, Can I create a macro that goes let's say from the first text box to
about 55,000 and delete them one at a time? Please advise. Thank you. Tony.
 
This code will work for you assuming that you want to delete all of the
shapes on the sheet. Shapes include text boxes and combo boxes and pictures
and command buttons and...

Sub DeleteStuff()
Dim shp As Shape

For Each shp In ActiveSheet.Shapes
shp.Delete
Next shp
End Sub
 
This code will delete ALL TextBoxes (whether from the Drawing toolbar or
from the ActiveX ControlToolbox toolbar) on the specified worksheet (change
my example worksheet name from the Sheet7 that I used to your worksheet's
actual name)...

Sub DeleteAllTextBoxesOnSheet()
Dim O As OLEObject
With Worksheets("Sheet7")
.TextBoxes.Delete
For Each O In .OLEObjects
O.Delete
Next
End With
End Sub
 
Slight modification to speed it up for lots of shapes:

Sub DeleteStuff()
Dim shp As Shape

Application.ScreenUpdating = False
For Each shp In ActiveSheet.Shapes
shp.Delete
Next shp
Application.ScreenUpdating = True

End Sub
 
Thanks Jim and everybody for your support. The sheet contained more than
52,000 text boxes and I could get rid of all of them. I just do not know how
in the world this may have happened. Thanks again.
Tony.
 
Back
Top