VBA & Macro Problems

How to Delete Specific Excel Shapes Over Merged Cells Using VBA

Maira Mehtab Sep 24, 2026 871 views

Question details

The user needs a VBA solution to delete copied rectangle shapes located over a specific section of merged cells without removing other essential background shapes, buttons, or images on the worksheet.

Product: Excel
Device & OS: not provided
Scenario: Automating the removal of specific duplicate or copied shapes in a designated worksheet area.
Observed behavior: The goal is to selectively delete shapes based on their row position (using TopLeftCell.Row) via a macro button, leaving unrelated worksheet buttons and background graphics intact.

Before you start

Ensure you have saved a backup of your workbook (.xlsm) before running new VBA macros, as macro actions typically cannot be undone using the standard Undo feature.

Solution 1Recommended

Loop Through Shapes Using VBA TopLeftCell.Row Property

Use a VBA loop to check the position of each shape and delete only the ones located within your target rows.

By iterating through the Shapes collection on the worksheet, you can check the TopLeftCell property of each shape. If the shape's top-left corner falls within the target row numbers (your merged cells section), the macro will delete it while completely ignoring shapes in other areas.

Open the VBA Editor

Press ALT + F11 on your keyboard to open the Visual Basic for Applications editor.

Insert a New Module

Navigate to the top menu and click Insert > Module to create a blank workspace for your code.

Write the Looping Macro

Enter a VBA script that loops through 'ActiveSheet.Shapes'. Set up a 'For Each' loop and add an 'If' statement to check if 'Shape.TopLeftCell.Row' matches your target area (for example, >= 10 And <= 20).

Add the Delete Command

Inside the condition, add the 'Shape.Delete' command so that only shapes meeting your specified row conditions are removed.

Assign to a Button

Return to your Excel worksheet, go to the Developer tab, click Insert > Form Controls > Button, draw the button, and assign your newly created macro to it.

Filtering by Shape Type: You can add an additional condition to check 'Shape.Type' if you only want to delete specific types of shapes, like msoAutoShape, while explicitly ignoring pictures and charts.

Advanced Spreadsheet Automation

Automate Shape Management with WPS Spreadsheet VBA

WPS Office offers robust VBA and Macro support, allowing you to seamlessly run scripts to manipulate shapes, automate data entry, and streamline your workflow completely for free.

1. Open Your Workbook: Download and launch WPS Office, then open your macro-enabled spreadsheet.
2. Access Developer Tools: Navigate to the Developer tab located on the top ribbon.
3. Open Visual Basic: Click on the Visual Basic icon to access the VBA editor and paste your shape-deletion script.
4. Run or Assign the Macro: Click Run inside the editor or assign the macro to a custom worksheet button for easy access.

Fully compatible with Microsoft Excel VBA macros (.xlsm and .xlsb formats)Lightweight and fast execution of automated scripts for shape manipulationEasy-to-use Developer tools for managing complex worksheet objects

microsoft office alternative - wps office

Frequently Asked Questions

Can I delete shapes based on their specific name instead of cell position?

Yes, you can check the 'Shape.Name' property within your VBA loop. If the name matches a specific string or pattern (e.g., 'Rectangle 1'), you can trigger the Delete method for that shape only.

Why are shapes placed over merged cells difficult to select with VBA?

Merged cells can cause standard range-based object selections to behave unpredictably. Using the TopLeftCell property is the most reliable method to anchor a shape's position to a specific row or column, bypassing merged formatting issues.

Will deleting shapes via macro accidentally delete my charts?

It might, as charts are also treated as part of the Shapes collection in VBA. To avoid deleting charts or functional buttons, you should explicitly verify the Shape.Type property and exclude types like msoChart or msoFormControl.

How do I enable the Developer tab to run VBA macros?

To access VBA tools, go to Menu > Options > Customize Ribbon, and check the box next to Developer. This will instantly add the Developer tab to your main interface.

Excel & Data Problems