Clearing Drawing Objects from a given Excel range

Amjad.Mahmoood · September 7th, 2006, 01:30 AM

Sub wipeoff()
Do While ActiveSheet.Shapes.Count <> 0
ActiveSheet.Shapes(1).Delete
Loop
End Sub

The above macro clears all pictures/drawing objects from the active sheet.

Now my problem is that I wish to change this macro so that removal of shapes/picture objects is restricted to the user given selection only (not the entire sheet).

Please help.
Thank you in advance

BrianWren · September 8th, 2006, 10:07 AM

Iâve never tried this, but it seems that

Code:

Sub WipeOff()

    Do While ActiveSheet.Selection.Shapes.Count > 0
        ActiveSheet.Selection.Shapes(1).Delete
    Loop

    ' Or

    Do While Selection.Shapes.Count > 0
        Selection.Shapes(1).Delete
    Loop

End Sub

should do what you need.

Amjad.Mahmoood · September 11th, 2006, 12:31 AM

Thank you, but this does not work, it generates error saying "object does not support this property or method"

Amjad.Mahmoood · September 11th, 2006, 01:06 AM

Thanks to Maccas (from MS Office Forum) and BrianWren here for contributing into the settlement of query.

The problem is solved as I made some adjustments in Maccas's macro as follows.

Sub testit()
WipeOffRng Selection
End Sub

Sub WipeOffRng(WipeRange As Range)
Dim isect As Range
Dim wkSheet As Worksheet
Dim Shp As Shape
Dim rngShp As Range

    ' Set the worksheet
    Set wkSheet = WipeRange.Parent

    ' Loop through every shape
    For Each Shp In wkSheet.Shapes

        ' Dtermine the block range
        Set rngShp = Range(Shp.TopLeftCell, Shp.BottomRightCell)

        ' Test for any sort of overlap
        'If Not Intersect(WipeRange, rngShp) Is Nothing Then
        ' Test for fully inside

        Set isect = Intersect(WipeRange, rngShp)
        If isect Is Nothing Then

            Else
            Shp.Delete

        End If

    Next Shp

End Sub