select and delete all pictures in a given range

[the excel-arator]

I would like to be able to have VBA for excel delete all the pictures on a
given worksheet, but only in a certain range of that sheet. Is this possible?
ie. delete the pictures found only in the range A5:C25

Worksheets("Sheet2").Range("A5:C25").Shapes.SelectAll
Selection.Delete

'That baby doesn't work, but is there perhaps something similar that might
do the trick?

Any help is appreciated,

Thanks!

John

 

[Gary''s Student]

Pictures "float" above the cells.

But you can find if they "hide" cells:

http://groups.google.com/group/micr...gary's+student"+group:*excel#87539a3aca31dcd7

 

[Peter T]

Delete all pictures partly or entirely within A5:C25 on Sheet2 of
activeworkbooik -

Sub test()
Dim s As String
Dim pic As Picture
Dim rng As Range

' Set ws = ActiveSheet
Set ws = ActiveWorkbook.Worksheets("Sheet2")

Set rng = ws.Range("A5:C25")

For Each pic In ActiveSheet.Pictures
With pic
s = .TopLeftCell.Address & ":" & .BottomRightCell.Address
End With
If Not Intersect(rng, ws.Range(s)) Is Nothing Then
pic.Delete
End If
Next

End Sub

Regards,
Peter T

 

[the excel-arator]

Just what I needed, thanks!

 

[amuthukumar]

Delete all pictures partly or entirely within A5:C25 on Sheet2 of
activeworkbooik -

Sub test()
Dim s As String
Dim pic As Picture
Dim rng As Range

' Set ws = ActiveSheet
Set ws = ActiveWorkbook.Worksheets("Sheet2")

Set rng = ws.Range("A5:C25")

For Each pic In ActiveSheet.Pictures
With pic
s = .TopLeftCell.Address & ":" & .BottomRightCell.Address
End With
If Not Intersect(rng, ws.Range(s)) Is Nothing Then
pic.Delete
End If
Next

End Sub

Regards,
Peter T

perfect one!! kudos to your knowledge.. this worked perfect for me
too!!! Hip hip hurray!!

 

[Peter T]

see important correction below -

perfect one!! kudos to your knowledge.. this worked perfect for me
too!!! Hip hip hurray!!

And now two satisfied customers....

But after a glance at the code as posted I regret to advise there is an
error. It would only become apparent if not dealing with the activesheet.

Please change
For Each pic In ActiveSheet.Pictures
to
For Each pic In ws.Pictures

Regards,
Peter T

 

[Vincnet]

see important correction below -

<[email protected]> wrote in message
news:eeed7d1c-b3de-49e1-a90a-4ab3f9ba50b2@h11g2000prf.googlegroups.com...
> On Jan 17, 5:17 pm, "Peter T" <peter_t@discussions> wrote:
> > Delete all pictures partly or entirely within A5:C25 on Sheet2 of
> > activeworkbooik -
> >
> > Sub test()
> > Dim s As String
> > Dim pic As Picture
> > Dim rng As Range
> >
> > ' Set ws = ActiveSheet
> > Set ws = ActiveWorkbook.Worksheets("Sheet2")
> >
> > Set rng = ws.Range("A5:C25")
> >
> > For Each pic In ActiveSheet.Pictures
> > With pic
> > s = .TopLeftCell.Address & ":" & .BottomRightCell.Address
> > End With
> > If Not Intersect(rng, ws.Range(s)) Is Nothing Then
> > pic.Delete
> > End If
> > Next
> >
> > End Sub
> >
> > Regards,
> > Peter T
> >

<snip>

> perfect one!! kudos to your knowledge.. this worked perfect for me
> too!!! Hip hip hurray!!

And now two satisfied customers....

But after a glance at the code as posted I regret to advise there is an
error. It would only become apparent if not dealing with the activesheet.

Please change
For Each pic In ActiveSheet.Pictures
to
For Each pic In ws.Pictures

Regards,
Peter T

Hi Peter,
And thanks for answering a sticky problem.
I find it works SOMETIMES. Any Idea why I might be getting the error message "Method "TopLeftCell' of object 'Picture' failed"?

Vince

 

[Mohammed Siddique]

Vince, I have changed the code slightly and its working fine for me ...

Sub test(rng As Range)
Dim s As String
Dim pic As Picture

Set WS = ActiveWorkbook.Worksheets("Siddique")

For i = 1 To WS.Pictures.Count
With WS.Pictures(i)
s = .TopLeftCell.Address & ":" & .BottomRightCell.Address
End With
If Not Intersect(rng, WS.Range(s)) Is Nothing Then
pic(i).Delete
End If
Next

End Sub

Sub abcd()
Call test(Sheets("siddique").Range("A41"))
End Sub

 

[suresh448online]

I tried this code but it s showing error at "pic(i).Delete" line. error is "Object variable or with variable not set"