Delete shapes in a range

P

Pam

I'm trying to delete shapes in a range. I have found this code from another
post, but it won't accept "TopLeftCell" (It says method or data member not
found) so I put in the range I wanted it to look in, but it deletes all the
shaps. Can you help me?

Dim sh as Worksheet, sh1 as Worksheet
Dim v as Variant, v1 as Variant
Dim rng as Range, rng1 as Range
Dim shp as Shape, i as Long, j as Long
set sh = Worksheets("Sheet1")
set rng = sh.Range("A11, B22, C33")
for each shp in sh.Shapes
set rng1 = sh.range(sh.TopLeftCell, sh.BottomRightCell)
if not intersect(rng,rng1) is nothing then
shp.Delete
end if
Next
 
B

Bob Phillips

TopLeftCell is a property of the shape, not the sheet

Dim sh As Worksheet, sh1 As Worksheet
Dim v As Variant, v1 As Variant
Dim rng As Range, rng1 As Range
Dim shp As Shape, i As Long, j As Long
Set sh = Worksheets("Sheet1")
Set rng = sh.Range("A11, B22, C33")
For Each shp In sh.Shapes

Set rng1 = sh.Range(shp.TopLeftCell, shp.BottomRightCell)
If Not Intersect(rng, rng1) Is Nothing Then

shp.Delete
End If
Next
 
D

Dave Peterson

Both the .topleftcell and .bottomrightcell belong to the shp, not the sh
variable.

set rng1 = sh.range(shp.TopLeftCell, shp.BottomRightCell)

And I would have expected this line:

Set rng = sh.Range("A11, B22, C33")
to look more like:
Set rng = sh.Range("A11:C33")
If you wanted to check all of the cells in A11 to C33????
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top