|
Loop to delete shapes
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? |
Loop to delete shapes
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? |
Loop to delete shapes
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? |
Loop to delete shapes
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? |
Loop to delete shapes
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? |
Loop to delete shapes
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? |
Loop to delete shapes
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? |
Loop to delete shapes
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? |
Loop to delete shapes
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 -- Rick (MVP - Excel) "Ron de Bruin" wrote in message ... 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? |
| All times are GMT +1. The time now is 06:33 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com