Deleting cliparts in excel sheet

[Karina]

I have an excel sheet in wich I paste everyday new information, replacing the
previous day information. There's a clipart that has been pasted over and
over, now the file is very slow and I can't erase all the cliparts
(thousands). In excel 2003 it was possible to select all the cliparts in one
step, now in 2007 I can't and it's impossible for me to erase the cliparts
one by one.

Please help!

 

[Don Guillett]

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

 

[Karina]

Already sent you the file, thanks.

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)


.

 

[Don Guillett]

Sub NOSHAPES()' Removes ALL SHAPES
For Each WS In ThisWorkbook.Sheets
For Each SH In WS.Shapes
SH.Delete
Next SH
Next WS
End Sub

 

[Ron de Bruin]

Warning :
I suggest not to use code like this because it is possible that

It will delete the AutoFilter dropdowns
It will delete the Data>Validation(List option) dropdowns
Excel crash if there are comments on the sheet

See this page for other code
http://www.rondebruin.nl/controlsobjectsworksheet.htm

 

[Don Guillett]

Ron, OP sent me the workbook which had none of these problems so I sent a
simple solution..

 

[Ron de Bruin]

Ok, but I would go for this to be sure

Sub Shapes1()
'Delete all Objects except Comments
On Error Resume Next
ActiveSheet.DrawingObjects.Visible = True
ActiveSheet.DrawingObjects.Delete
On Error GoTo 0
End Sub

 

[Don Guillett]

Good point