Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Trying to delete multiple stacked objects

0

In the attached filel I have arrows that have been copied on top of each other multiple times. When I right click and select cut, it cuts out one but there are as many as 80 others right underneath. 

The arrow labeled 1 is a single arrow that I moved from the stack (labeled 2). I can't just select all the objects on the sheet and select delete as i hace multiple other objects on the sheet that I don't want deleted.

Is there a way to delete all the objects just in the stack labeled 2 without deleting them 1 by 1?

Thank you

Mark

Answer
Discuss

Discussion

Mark. Did you see my Answer below? If so, please respond.
John_Ru (rep: 6142) Dec 20, '23 at 10:06 am
Did you forget to respond to my Answer, Mark?
John_Ru (rep: 6142) Jan 13, '24 at 12:55 pm
Add to Discussion

Answers

0

Hi again Mark

You can use VBA and the TopLeftCell property of Shapes to delete stacked shapes.

In the attached file (a macro-enabled revision of yours), I've put a blue button near cell D160 (for demo purposes) which states "To delete shapes, select or move to the  top left cell then click here". With U159 selected (say), clicking the button runs the code in Module1 as follows:

Sub DelStacked()

    Dim Shp As Shape, Resp As VbMsgBoxResult

    ' check single cell selected
    If Selection.Count > 1 Then
        MsgBox "To delete shapes, please select a single cell "
        Exit Sub
    End If
    ' check user want to delete
    Resp = MsgBox("Delete all (stacked) shapes, near " & Selection.Address(0, 0) & ", press Ok", vbOKCancel)
    If Resp = vbCancel Then Exit Sub

    ' if so, loop through shapes and delete those with same address and count
    For Each Shp In ActiveSheet.Shapes
        If Shp.TopLeftCell.Address = Selection.Address Then
            Shp.Delete
            Qty = Qty + 1
        End If
    Next Shp
    ' tell user result
    MsgBox Qty & " shape(s) deleted near cell " & Selection.Address(0, 0)

End Sub

It reports that 206 shapes (not a maximum of 80!) were deleted. Click it when near a a single shape and it will tell you it deleted just 1. You just need to get to the cell nearest the top left of the shape. 

Hope this fixes things for you (and saves lots of time). If so, please be sure to mark this Answer as Selected.

Discuss

Discussion

Did you use that solution? If so, please remember to mark the Answer as Selected. 
John_Ru (rep: 6142) Jan 1, '24 at 3:11 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login