John Carney
asked on
Determining the order of shape selection in a Delete Shapes macro
I find that the macro below never starts with the uppermost left shape, but rather goes through every other shape in the active sheet BEFORE targeting the ones I want to delete. How do I get it to start with the left uppermost shape in the specified range and/or at least ignore any shape outside of the range?
Thanks,
John
Thanks,
John
Sub DeleteSomeShapes()
Dim shp As Shape, top As Range, btm As Range
Set top = [B2]
Set btm = Cells([A4] - 7, 18)
For Each shp In ActiveSheet.Shapes
If Not Intersect(shp.TopLeftCell, Range(top, btm)) Is Nothing Then
shp.Delete
End If
Next shp
End Sub
Assuming A4 has the right number in it, if I test that code it only deletes shapes in the specified range.
ASKER
Hi Stephen, yes it does that for me too. It's just that I always have to run it on a bunch of sheets and it does slow things down a little, and every once in a while it bugs for some reason and testing it is annoyingly time-consuming.
Is there anyway to keep this kind of a macro from looking at all the extraneous shapes. What would be ideal would be something that says in effect: ActiveSheet.Range("B2:R100 ").Drawing Objects.De lete
Thanks,
John
Is there anyway to keep this kind of a macro from looking at all the extraneous shapes. What would be ideal would be something that says in effect: ActiveSheet.Range("B2:R100
Thanks,
John
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Brad, thanks. Rest assured I'm not selecting any sheets! At the moment I only need to delete shapes from the active sheet, but thank you for the additional code.
I'm probably making a mountain out of a mole hill, but for some reason this macro bugged this morning and I always test codes like this with cel.Select or shp.select so that I can see the steps it's going through. Whenever I have to do that, it just annoys me that this macro invariably selects all the shapes outside the desired range before getting to the ones that matter.
As soon as I can get back into my network drive I'll test this out to see if there's some tweak in your first code that will ignore the out of bounds shapes. If not, then I will do everything in my power to get out of denial and simply let mole hills be mole hills :-)
Thanks,
John
I'm probably making a mountain out of a mole hill, but for some reason this macro bugged this morning and I always test codes like this with cel.Select or shp.select so that I can see the steps it's going through. Whenever I have to do that, it just annoys me that this macro invariably selects all the shapes outside the desired range before getting to the ones that matter.
As soon as I can get back into my network drive I'll test this out to see if there's some tweak in your first code that will ignore the out of bounds shapes. If not, then I will do everything in my power to get out of denial and simply let mole hills be mole hills :-)
Thanks,
John
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great analysis and a wealth of useful info, thanks Brad!
- John
- John