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

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" <(E-Mail Removed)> wrote in message
news:957EEB7D-B62D-4C14-9B9A-(E-Mail Removed)...
> 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
>

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

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
>