Barb's code just showed you how to use .topleftcell.address (and
..bottomrightcell.address).

She didn't include the code to check to see if the shapes were in your range --
or the code to delete it if it were in that range.

Francis Hookham wrote:

Thank you Barb - that was prompt response. I have inserted your suggestion
into the macro I had written - here it is but it does not help - the text
boxes are still squashed up to the top edge of the range.

Any suggestions?

Francis

Dim myShape As Shape

'Remove an unwanted scene
Sub DeleteScene()
'prevent wasting time but stopping updating of screen
Application.ScreenUpdating = False
'find and select insertion point
Count = ActiveCell.Row
While Cells(Count, 2) < "Scene:" 'a sure way of finding the top row,
whichever cell had been selected
Count = Count - 1
Wend
Cells(Count, 1).Select

Range(Cells(Count - 1, 1), Cells(Count + 16, 8)).Select

For Each myShape In ActiveSheet.Shapes
Debug.Print myShape.Name, myShape.Placement, _
myShape.BottomRightCell.Address,
myShape.TopLeftCell.Address
Next myShape

'select 17 rows and delete them
ActiveCell.Rows("1:17").EntireRow.Delete Shift:=xlUp
'reset formulas
Cells(ActiveCell.Row, 3) = "=R[-17]C+0.01"
Cells(ActiveCell.Row, 7) =
"=IF(ISNUMBER(RC[-2]),R[-17]C+RC[-2]/86400,"""")"
'renumber frames
RenumberFrames
'run SetPrintArea macro
SetPrintArea
End Sub

"Barb Reinhardt" wrote in message
...
I'm guessing you could gather the TopLeftCell and the BottomRightCell of
the
shape and delete using that.

Dim myShape as shape
For Each myShape In ActiveSheet.Shapes
Debug.Print myShape.Name, myShape.Placement, _
myShape.BottomRightCell.Address,
myShape.TopLeftCell.Address
Next myShape

HTH
--
HTH,
Barb Reinhardt



"Francis Hookham" wrote:

Several rows need to be deleted but the area contains some text boxes
which
are not deleted - they are 'squashed up' between the row above and the
row
below the range deleted. I do not know if it will matter if they remain
but
I should like to get rid of them. The trouble is they cannot be selected
because their names vary.

This recorded macro worked, of course, for this unique arrangement:

Sub Macro3()
ActiveSheet.Shapes.Range(Array("Text Box 5", "Text Box 1730", "Text
Box
1732", _
"AutoShape 1734")).Select
Selection.Delete
ActiveCell.Rows("1:17").EntireRow.Select
Selection.Delete Shift:=xlUp
ActiveCell.Select
End Sub

Is there a way of selecting ANY objects within an range of cells
irrespective of their names - a 'Select All' between RowX and RowY? If so
whatever was gathered up in this way could be deleted.

Francis Hookham




--

Dave Peterson