Delete shapes in a range
 

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

Delete shapes in a range
 

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


--
__________________________________
HTH

Bob

"Pam" wrote in message
...
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

Delete shapes in a range
 

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????

Pam wrote:

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

--

Dave Peterson

Delete shapes in a range
 

Thank you both very much. I got it to work.

Pam

"Dave Peterson" wrote:

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????

Pam wrote:

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

--

Dave Peterson