Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add and delete pictures and shapes | Excel Discussion (Misc queries) | |||
Delete shapes, not buttons | Excel Programming | |||
Delete all Shapes from selected Range | Excel Programming | |||
HOW DO I DELETE SHAPES THAT ARE IN SPREADSHEETS? | Excel Discussion (Misc queries) | |||
Delete Shapes | Excel Programming |