Deleting "hidden" charts on a worksheet

[LJLevine]

Hi everyone,

I've been playing around w/ a worksheet that contains data and charts,
inserting and removing multiple rows at a time. Unfortunately, I did
not change the properties of the charts and they were all set to "Move
and Size w/ Cells". When I deleted a series of rows, these charts
disappeared and left a line across the spreadsheet where they used to
be. I've tried everything to get rid of them (because they are now
calculating incorrectly and giving me error messages), including
deleting the rows where they appear, trying to select them on the
sheet, but nothing works. The best scenario would be to select and
delete them but obviously this isn't possible. Any thoughts?

Thanks,
Louis

 

[Don Guillett]

Try this. Be advised it will delete all shapes on the active sheet. Is this
what you want to do?
Sub deleteshapes()
For Each s In ActiveSheet.Shapes
s.Delete
Next s
End Sub

 

[Jon Peltier]

A little more discriminating:

Sub DeleteThinShapes()
Dim s As Shape
For Each s In ActiveSheet.Shapes
If s.Height < 1 or s.Width < 1 Then
s.Delete
End If
Next
End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Guest]

Hello John. I read your note and the code worked perfectly (as designed) but
I have a bunch of charts that are hidden (don't know why). Even when I use
your discriminating code it gets all charts, buttons, everything. Is there a
way to find them and delete them one at a time? Thanks in advance. Carl

 

[Andy Pope]

Hi,

Jon's code should only be deleting shapes that have a width or height of
less than 1., so I'm not sure why it's deleting everything for you.

This small mod will require confirmation before deleting. It will also
highlight the cells that the shape in covering.

Sub DeleteThinShapes()
Dim s As Shape
Dim strMsg As String
Dim rngOrig As Range

Set rngOrig = ActiveCell
For Each s In ActiveSheet.Shapes
If s.Height < 1 Or s.Width < 1 Then
Application.Goto Range(s.TopLeftCell, s.BottomRightCell)
strMsg = "Delete " & s.Name & _
" which is over cells " & Selection.Address
If MsgBox(strMsg, vbYesNo) = vbYes Then s.Delete
End If
Next
rngOrig.Select

End Sub

Cheers
Andy