+ Reply to Thread
Results 1 to 11 of 11

Delete all shapes in specified range with macro

  1. #1
    Registered User
    Join Date
    03-14-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    6

    Delete all shapes in specified range with macro

    Hi all,

    I need a macro to delete all shapes in a specified range of cells (C13:P35) on the first sheet of my workbook. I have a macro that does this and works perfectly when I just run it by itself. Here is the code:

    Please Login or Register  to view this content.
    My problem occurs when I want to run this macro along with others on Sheet1. Here is the code I am attempting to run:

    Please Login or Register  to view this content.
    Running the above without the "Call DeleteShapes" in each scenario works perfectly. When I add "Call DeleteShapes" it tells me I have an error in this line of code from my DeleteShapes macro:

    Please Login or Register  to view this content.
    Again, I do not get an error when I run this DeleteShapes macro by itself, only when I "Call" it. I am using Excel 2010 and don't really have any experience with macros. Thank you for any help you can give!

  2. #2
    Registered User
    Join Date
    03-14-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Delete all shapes in specified range with macro

    Does anyone have any ideas?

  3. #3
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Delete all shapes in specified range with macro

    Sure the problem is not with the code in the Change event. you appear to be missing an End If
    Cheers
    Andy
    www.andypope.info

  4. #4
    Registered User
    Join Date
    03-14-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Delete all shapes in specified range with macro

    Thank you for your reply, but I don't quite follow. Can you please explain where I am missing an End If?

  5. #5
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Delete all shapes in specified range with macro

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    03-14-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Delete all shapes in specified range with macro

    Oh, I see what you mean - thanks. I actually already have that End If in my full code. I just forgot to include it in the shortened version of my code that I posted in this thread. Good catch though, but not the problem.

  7. #7
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Delete all shapes in specified range with macro

    you need to post example as the example I'm creating works

  8. #8
    Registered User
    Join Date
    03-14-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Delete all shapes in specified range with macro

    When I run the below macro (DeleteShapes) by itself, it does what I want. i.e. deletes all shapes in the specified range

    Please Login or Register  to view this content.
    When I run the below Change event code which includes the DeleteShapes macro, it returns a 1004 Run-time error:

    Please Login or Register  to view this content.
    When I hit Debug on the error, It highlights the following code in the DeleteShapes macro:

    Please Login or Register  to view this content.
    Is there any way to diagnose which specific part of the above line of code is causing the error? I suspect it might have to do with the TopLeftCell section.

  9. #9
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Delete all shapes in specified range with macro

    Check the components whilst debugging, as I said my example works so it's really hard to say what your exact problem is without an example to test.

    What is the address of shp.Topleftcell?

  10. #10
    Registered User
    Join Date
    03-14-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Delete all shapes in specified range with macro

    How do I determine what the address of shp.Topleftcell is? I don't have much experience with macros so I don't really know my way around. I don't want to post my workbook on this forum, but can I email it to you possibly? I have a feeling this is a very simple problem.

  11. #11
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Delete all shapes in specified range with macro

    In immediate window type

    ?shp.Topleftcell.address

    Post an example rather than a workbook full of sensitive data.
    See my websites contact page for email address if you must send file.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1