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?

Try

Activesheet.shapes.selectall
Selection.delete

If this post helps click Yes
---------------
Jacob Skaria


"Daniel Bonallack" wrote:

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?

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" wrote in message ...
Try

Activesheet.shapes.selectall
Selection.delete

If this post helps click Yes
---------------
Jacob Skaria


"Daniel Bonallack" wrote:

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?

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
--
If this post helps click Yes
---------------
Jacob Skaria


"Ron de Bruin" wrote:

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" wrote in message ...
Try

Activesheet.shapes.selectall
Selection.delete

If this post helps click Yes
---------------
Jacob Skaria


"Daniel Bonallack" wrote:

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, 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

"Jacob Skaria" wrote:

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
--
If this post helps click Yes
---------------
Jacob Skaria


"Ron de Bruin" wrote:

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" wrote in message ...
Try

Activesheet.shapes.selectall
Selection.delete

If this post helps click Yes
---------------
Jacob Skaria


"Daniel Bonallack" wrote:

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?

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

--
Rick (MVP - Excel)


"Jacob Skaria" wrote in message
...
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
--
If this post helps click Yes
---------------
Jacob Skaria


"Ron de Bruin" wrote:

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" wrote in message
...
Try

Activesheet.shapes.selectall
Selection.delete

If this post helps click Yes
---------------
Jacob Skaria


"Daniel Bonallack" wrote:

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?

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 DataValidation(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" wrote in message ...
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

--
Rick (MVP - Excel)


"Jacob Skaria" wrote in message
...
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
--
If this post helps click Yes
---------------
Jacob Skaria


"Ron de Bruin" wrote:

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" wrote in message
...
Try

Activesheet.shapes.selectall
Selection.delete

If this post helps click Yes
---------------
Jacob Skaria


"Daniel Bonallack" wrote:

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?

Wasn't this answered a few days ago?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Daniel Bonallack" wrote in
message ...
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?