Automate Excel

VBA: Delete All Autoshapes

The following is a Macro to delete all of the Autoshapes on a given worksheet.

Sub DeleteAllShapes()

'Activate sheet to delete autoshapes.
Sheet1.Activate

Dim GetShape As Shape

    For Each GetShape In ActiveSheet.Shapes
        GetShape.Delete
    Next

End Sub

Posted in VBA |

One Response

1. Rob van Gelder Says:
   December 2nd, 2004 at 12:10 am

   Hi.

   Along a similar line.
   I built this code to delete shapes within a given range.

   Setting cDeleteOnTouch to True has a different effect.
   ie. Range is just touching or range fully covers shape?

   Sub test()
       Const cDeleteOnTouch As Boolean = False
       Dim rng As Range, shp As Shape, rngSelect As Range, blnDelete As Boolean
   
       Set rngSelect = Selection
   
       For Each shp In ActiveSheet.Shapes
           blnDelete = False
           Set rng = Intersect(Range(shp.TopLeftCell, shp.BottomRightCell), rngSelect)
           If cDeleteOnTouch Then
               If Not rng Is Nothing Then blnDelete = True
           Else
               If Not rng Is Nothing Then
                   If rng.Address = Range(shp.TopLeftCell, shp.BottomRightCell).Address Then blnDelete = True
               End If
           End If
   
           If blnDelete Then
               MsgBox "delete " & shp.Name
               'shp.Delete
           End If
       Next
   End Sub

Leave a Comment