We help IT Professionals succeed at work.

Why doesn't "i" Work with Pictures

I have a worksheet that has several "Pictures" or, Picture objects, on it that I would like to remove.

In running the Macro Recorder, it lists these objects as ActiveSheet.Shapes("Picture #") or
ActiveSheet.Shapes("Picture 1").Select

Open in new window

In putting together some code to delete these I used - This is the short version to my question:

Sub DeleteShapes()
Dim shp As Shape, i As integer
i = 1
   For Each shp In ActiveSheet.Shapes
     ActiveSheet.Shapes("Picture [b]i[/b]").Delete
    i = i + 1
   Next
i = 0
End Sub

Open in new window


That doesn't work though, it doesn't recognize the "i".  However, If I replace the i with a 1, then it will work.  I have tried changing i to Long, String, etc. Even though i = 1 is the right value, the code won't allow it to excecute, it has to be the actual number.  Why is this?  It defeats the whole purpose for variables.
Comment
Watch Question

Top Expert 2008
Commented:
You can run this directly.

Sub DeleteShapes()
Dim shp As Shape
   For Each shp In ActiveSheet.Shapes
     shp.Delete
   Next
End Sub

Open in new window


For your code to work you'd need to write the following, otherwise your variable name is just considered as part of the string.
ActiveSheet.Shapes("Picture " & i ).Delete

Open in new window


Thomas

Author

Commented:
Thanks, your result worked better than what I had put together, but there was one oddity.  When I tried to run your code initially, I got an error that "Cannot Enter into Break Mode at this Time."  When I added the On Error Resume Next, then it worked fine.  That was the first time I've seen that error, have you seen it before?

Author

Commented:
I think I may have figured out the error, there are some Merged Cells and it may have errored out on those.  That's the thought anyway.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.