A little trouble with shapes

fredrerik84

Active Member
Joined
Feb 26, 2017
Messages
383
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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Are the shapes you want to keep the only rounded rectangles on the sheet?
 
Upvote 0
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
 
Upvote 0
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 ..."?
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top