VBA & Macro Problems

Excel VBA: Delete Shapes and Arrows from a Selected Range

Jason MillerJason Miller Nov 12, 2026 868 views

Question details

The user needs to remove the arrows and line shapes currently located in a selected Excel worksheet area with VBA.

Product
Microsoft Excel VBA
Device & OS
not provided
Scenario
A recorded macro must be adapted to work with the shapes that currently exist in the selected range.
Observed behavior
The recorded macro returns runtime error 1004 because it references specific shape names that were present during recording but may no longer exist.
Before you start

Open the worksheet containing the shapes, select the exact cell range to clean, and confirm that the active sheet is correct. Save a copy of the workbook before running a macro that deletes shapes.

Solution 1Recommended

Delete current shapes that overlap the selected range

Use this approach when recorded shape names are unreliable and the macro should target any current shape or arrow overlapping the selection.

The macro checks each shape in the active worksheet's current Shapes collection. It compares the shape's top-left and bottom-right cells with Selection, so it does not depend on names captured by the recorder.

1
Open the VBA editor

In Excel, press Alt+F11, select Insert > Module, and create a standard module in the current workbook.

2
Add the range-based deletion macro

Paste this code into the module: 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

3
Run the macro on the selected area

Close the Visual Basic Editor, select the worksheet range containing the arrows or lines, then choose View > Macros, select DeleteShapesInSelection, and click Run.

Avoid recorded shape references: The macro evaluates the shapes currently on the worksheet rather than calling fixed names such as Shape 1 or Arrow 3, preventing errors when recorded shapes have been renamed or removed.

Use WPS Spreadsheet VBA to remove shapes from a selected range

WPS Spreadsheet can run a range-based VBA macro where VBA support is available. Insert the macro in the VBA editor, select the target cells, and run it to remove current arrows and line shapes without relying on recorded shape names.

  1. 1. Open the VBA editor: In WPS Spreadsheet, open the workbook and use the VBA editor command or press Alt+F11 if enabled, then choose Insert > Module.
  2. 2. Insert the deletion macro: Paste the DeleteShapesInSelection procedure into the standard module and save the macro-enabled workbook as an .xlsm file when prompted.
  3. 3. Select the target cells: Return to the worksheet and drag across the exact area containing the arrows or line shapes that should be removed.
  4. 4. Run the macro: Open the Macros dialog, choose DeleteShapesInSelection, and click Run to delete shapes overlapping the selected range.
Targets current shapes that overlap the selected cell rangeAvoids brittle recorded shape names that can trigger runtime errorsProvides a familiar spreadsheet interface with lightweight workbook editingSupports Microsoft Excel .xlsx and .xlsm file formats for easier migration
microsoft office alternative - wps office

Frequently Asked Questions

Why does the recorded Excel macro return runtime error 1004?

The recorder saves references to specific shape names. If those shapes were deleted, renamed, or replaced, VBA cannot find them and raises error 1004. Checking the current Shapes collection avoids fixed references.

Will this macro delete shapes outside the selected cells?

It is intended to delete only shapes whose top-left and bottom-right cells overlap the selected range. Check the selection carefully before running it, especially for shapes that extend across a boundary.

Can I delete only arrows and lines instead of every shape?

Yes. Add a condition based on shp.Type or the shape's AutoShapeType before calling shp.Delete. Test the type values on a copy because connector and line types can vary.

What should I do if the macro skips a shape after deleting another one?

Deleting items while using For Each can affect collection iteration in some VBA environments. If shapes are skipped, loop backward from wsh.Shapes.Count to 1 and delete by index after checking each shape.