CODEWITHSUNDEEP
excelvbashapes
jvo
jvo

Reputation: 327

Delete one autoshape in a range - VBA

This is what i try to do. When i click on one button, the autoshape above is copied in the A8:F12 rectangle.

The code for the 1st button(recorded macro):

Sub addTextbox1()
    Range("A2:C3").Select
    Range("C2").Activate
    Selection.Copy
    Range("B9").Select
    ActiveSheet.Paste
End Sub

My problem is i want to make a button which can erase the autoshape in the A8:F12 range. I found this which delete all autoshapes in the worksheet:

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

or delete an autoshape by its name (don't work in my case, new autoshapes are generated so i don't know their name)

I know it's fast to click on the shape and press 'delete' but by curiosity i wanted to know if it's possible to do it in VBA

Upvotes: 1

Views: 901

Answers (1)

brettdj
brettdj

Reputation: 55702

One way below is to test whether the top left cell of the shape lies in your deletion range:

Sub DeleteShapes()
Dim Shp As Shape
    For Each Shp In ActiveSheet.Shapes
         If Not Application.Intersect(Shp.TopLeftCell, ActiveSheet.Range("A8:F12")) Is Nothing Then Shp.Delete
    Next Shp
End Sub

Upvotes: 1

Related Questions