Why doesn't "i" Work with Pictures

Posted on 2012-08-16
Last Modified: 2012-08-16
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
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.
Question by:Cook09
    LVL 39

    Accepted Solution

    You can run this directly.

    Sub DeleteShapes()
    Dim shp As Shape
       For Each shp In ActiveSheet.Shapes
    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


    Author Closing Comment

    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 Comment

    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.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now