Solved: Deleting Autoshapes with vba

[Anomandaris]

Hi again,

This time its a relatively straightforward question. :)
Say I have several autoshapes on a worksheet and want to delete them all, whats the most efficient way to do it.

I know I can do it individually like this:



Activesheet.Shapes("AutoShape 1").Select
Selection.Delete
ActiveSheet.Shapes("AutoShape 2").Select
Selection.Delete


But this is too long if I have like 50 shapes.
Is there a quicker way like say
("AutoShape 1 To 50").Select
Selection.Delete

something like that would take less space


any advice would be greatly appreciated...thanks!

[mdmackillop]

Dim shp As Shape
For Each shp In ActiveSheet.Shapes
shp.Delete
Next

[Bob Phillips]

Be careful deleting shapes, the code Malcolm gave will delete dropdown arrows such as Dataa Validation, f you have any, and they are a devil to get back.

A more robust mechanism is




'----------------------------------------------------------------
Sub RemoveShapes()
'----------------------------------------------------------------
' Written by : Bob Phillips
' Inspired by: Debra Dalgleish & Dave Peterson
' Improved by: Dave Peterson (cater for forms combobox)
'---------------------------------------------------------------
' Synopsis: Checks each shape to be form control, and if it
' is a dropdown, it aims to retain it.
' One problem is taht the forms combobox which is
' also a form control, and is a dropdown, so it
' does not get deleted.
'
' Catered for by testing top left of shape, as
' Autofilter and Data Validation dropdowns do not
' seem to have a topleftcell address.
'---------------------------------------------------------------
Dim shp As Shape
Dim sTopLeft As String
Dim fOK As Boolean

For Each shp In ActiveSheet.Shapes

fOK = True

sTopLeft = ""
On Error Resume Next
sTopLeft = shp.TopLeftCell.Address
On Error GoTo 0

If shp.Type = msoFormControl Then
If shp.FormControlType = xlDropDown Then
If sTopLeft = "" Then
fOK = False 'keep it
End If
End If
End If

If fOK Then
shp.Delete
End If

Next shp

End Sub

[Anomandaris]

Thanks a lot madmac and xld....it works, i tried both, but will be using yours xld, as u say it seems the safer option.

One thing though when go from one sheet to another, sometimes the command buttons(autoshapes with macros attached to them) start blinking for 3-4 seconds, doesnt happen everytime but it can be disturbing to the user i suppose. Is there anyway to stop that?