Loop to delete shapes

[Daniel Bonallack]

I copy data from a webpage to Excel, then clear the shapes and icons off
using this loop. I know, it's so lame and inefficient, especially as there
are usually only 30 or so shapes to delete.

On Error Resume Next
For i = 1 To 2000
ActiveSheet.Shapes("Picture " & i).Select
Selection.Delete
Next i

What I would like is a loop that deletes each image without the redundant
work that the above loop goes through.

Can you help?

 

[Jacob Skaria]

Try

Activesheet.shapes.selectall
Selection.delete

If this post helps click Yes

 

[Don Guillett]

Wasn't this answered a few days ago?

 

[Ron de Bruin]

Hi Jacob

Your code will not select all shapes types
Maybe no problem for the OP but use this if you want to delete them all (also working OK in 2007)

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

Use this to delete comments

Sub Comments()
'This will delete all comments
ActiveSheet.Cells.ClearComments
End Sub


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

 

[Jacob Skaria]

Thanks Ron..

Daniel, if you are worried about the number of iterations you can get the
..count property to get the number; as in below example

On Error Resume Next
Dim i as Integer
For i = 1 To ActiveSheet.Shapes.Count
ActiveSheet.Shapes(i).Delete
Next i


Just to add on

 

[Daniel Bonallack]

Jacob, Ron,

Thanks very much for your replies - very helpful (not just for my immediate
problem, but for related issues).

And Don, my apologies, I normally do first search for a solution to avoid
duplication, but this time I didn't.

regards
Daniel

 

[Rick Rothstein]

My preference for iteration of this type is to use the For Each construct...

Dim S As Shape
For Each S In ActiveSheet.Shapes
S.Delete
Next

 

[Ron de Bruin]

Not use that Rick

See why on this page
http://www.rondebruin.nl/controlsobjectsworksheet.htm

Part of the webpage:

Not use code like below 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

Note: Not every Excel versions have all problems.

Sub NotUseThisMacro()
'Loop through the Shapes collection
Dim myshape As Shape
For Each myshape In ActiveSheet.Shapes
myshape.Delete
Next myshape
End Sub

Good night


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

 

[Rick Rothstein]

Yes, I knew that, but had forgotten it. Thanks for the reminder. However,
given the OP's posted code and description, it looks like all he wants to do
is delete pictures. If you assume that is true, then I think this For..Each
loop will safely do what he wants...

Dim S As Shape
For Each S In ActiveSheet.Shapes
If S.Type = msoPicture Then S.Delete
Next