Delete all the shapes on a worksheet

We have been faced with projects / situations where multiple pages have become clogged up with numerous and usually superfluous or duplicated items such as shapes, macro buttons etc on a worksheet. These shapes can take up memory and often get in the way of the worksheet in unpredictable ways. We have also seen copy macros go wrong and duplicated hundreds, sometimes thousands of shapes that were inadvertently copied in the code as they were within the cell ranges selected – sometimes visible, sometimes not.

Situations like these can take hours to manage and return to normal, when it may be more efficient to remove or delete all known shapes on a worksheet and start again with new buttons, a new shape etc. This very small piece of code will do just that on the activesheet. Copy this into a module and name the module appropriately so you can refer to it at any time you need to by putting the cursor at the beginning of the code and pushing F5 or clicking the Run button in the module. Make sure you’re on the activesheet you want to process though.

To remove charts is a little different and is dealt with in the next blog post. It’s just a small code change from the example below though, so nice and easy.

Leave us a comment below, or contact us for help and assistance if required using the form.

Sub Del_All_Shapes()
Dim sh As Shape
For Each sh In ActiveSheet.Shapes
sh.Delete
Next sh
End Sub

Contact us for some advice and guidance on how your Excel development could be created and start helping your business straight away. Contact Us