Forum Discussion

Arcade_Printing's avatar
Arcade_Printing
Copper Contributor
Oct 11, 2023

trying to record a macro to clear objects

trying to record a macro to clear a portion of a sheet of arrows. i can clear them by this procedure, Home->Find&Select->Select objects->highlight area->Delete->esc. but when i record it in a macro it doesnt work. get run time error '1004' the item with specified name wasnt found. I think it is looking for the arrows (arrows are made, Insert->shapes->lines) that were on sheet when I recorded macro. need it to search that portion of sheet for current arrows and delete. how can I do this.

excel
Macros and VBA
office 365
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Oct 11, 2023

    Arcade_Printing 

    Try this macro:

    Sub DeleteShapesInSelection()
    Dim wsh As Worksheet
    Dim shp As Shape
    Application.ScreenUpdating = False
    Set wsh = ActiveSheet
    For Each shp In wsh.Shapes
        If Not (Intersect(shp.TopLeftCell, Selection) Is Nothing Or _
                Intersect(shp.BottomRightCell, Selection) Is Nothing) Then
            shp.Delete
        End If
    Next shp
    Application.ScreenUpdating = True
End Sub

Resources