Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: A little trouble with shapes

  1. #1
    Board Regular
    Join Date
    Feb 2017
    Posts
    383
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default A little trouble with shapes

    Im trying to remove most of my shapes from my sheet but a few of them Id like to keep They are in cell S21 and ab21

    the range of cells i want to delete are stored in range(a27:BS & last row")

    Ive tried like this

    Code:
         Set Rng = Range(Cells(26, "C"), Cells(lr, "P"))
         
         For Each sh In ActiveSheet.Shapes
            On Error Resume Next
            If Intersect(Rng, sh.TopLeftCell) Is Nothing Then
            Else
               sh.Delete
           End If
         Next sh
    But this codes still deletes shapes in S21 and ab21 , which I like to keep. they are Rounded Rectangles 10 shapes total. No mater what I try they get deleted.

    Anyone know how I can keep them ?

    frederik

  2. #2
    MrExcel MVP
    Join Date
    May 2009
    Posts
    15,361
    Post Thanks / Like
    Mentioned
    13 Post(s)
    Tagged
    2 Thread(s)

    Default Re: A little trouble with shapes

    Are the shapes you want to keep the only rounded rectangles on the sheet?
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  3. #3
    Board Regular
    Join Date
    Feb 2017
    Posts
    383
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: A little trouble with shapes

    Hi thanks for your reply.

    No I also have:
    Plus, Down Arrow, Flowchart: Multidocument in row 3
    as well as rounded rectangles in row 24
    and an icon I have called topico in last row + 1

    Basically the only shapes I want to deleted are png shapes loaded from folder icon..

    They are placed in Col D, G, M and P Row 27 to LastRow

  4. #4
    Board Regular
    Join Date
    Feb 2017
    Posts
    383
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: A little trouble with shapes

    My code Keeps all the icons exept the once mention in post # 1

  5. #5
    Board Regular
    Join Date
    Feb 2017
    Posts
    383
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: A little trouble with shapes

    Ive tried a dozen attempts but not sure exactly how to write the code

  6. #6
    MrExcel MVP
    Join Date
    May 2009
    Posts
    15,361
    Post Thanks / Like
    Mentioned
    13 Post(s)
    Tagged
    2 Thread(s)

    Default Re: A little trouble with shapes

    Quote Originally Posted by fredrerik84 View Post
    Hi thanks for your reply.

    No I also have:
    Plus, Down Arrow, Flowchart: Multidocument in row 3
    as well as rounded rectangles in row 24
    and an icon I have called topico in last row + 1

    Basically the only shapes I want to deleted are png shapes loaded from folder icon..

    They are placed in Col D, G, M and P Row 27 to LastRow
    Do the png shapes have something unique in their names like "Picture ..."?
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  7. #7
    Board Regular
    Join Date
    Feb 2017
    Posts
    383
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: A little trouble with shapes

    there are 2000 shapes, think the name is generated random like "Picture 9"

    My latest attempt is this :

    Code:
         For Each sh In ActiveSheet.Shapes
            On Error Resume Next
            If Intersect(Rng, sh.TopLeftCell) Is Nothing Then
            Else
               If Not (sh.Type = msoOLEControlObject Or sh.Type = msoFormControl) Then _
               sh.Delete
           End If
         Next sh
    shouldnt this be working
    Last edited by fredrerik84; Nov 22nd, 2017 at 12:03 PM.

  8. #8
    Board Regular
    Join Date
    Feb 2017
    Posts
    383
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: A little trouble with shapes

    I figured it out. Was error somewhere else in the code...

  9. #9
    MrExcel MVP
    Join Date
    May 2009
    Posts
    15,361
    Post Thanks / Like
    Mentioned
    13 Post(s)
    Tagged
    2 Thread(s)

    Default Re: A little trouble with shapes

    Quote Originally Posted by fredrerik84 View Post
    there are 2000 shapes, think the name is generated random like "Picture 9"

    My latest attempt is this :

    Code:
         For Each sh In ActiveSheet.Shapes
            On Error Resume Next
            If Intersect(Rng, sh.TopLeftCell) Is Nothing Then
            Else
               If Not (sh.Type = msoOLEControlObject Or sh.Type = msoFormControl) Then _
               sh.Delete
           End If
         Next sh
    shouldnt this be working
    My point is if the shapes you want to delete all have names like "Picutre*" and the shapes you don't want deleted don't have "Picture" in their names you could try something like this:
    Code:
    Sub RemovePictures()
    Dim pic As Picture
    For Each pic In ActiveSheet.Pictures
        pic.Delete
    Next pic
    End Sub
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  10. #10
    Board Regular
    Join Date
    Feb 2017
    Posts
    383
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: A little trouble with shapes

    Was an "error" somewhere else in my code. Im working on a reset code of 2000 rows a lot to consider when writing the code. Anyways times for your swift resonses .. Have a great day :=)

    frederik

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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