how to test if range object target deleted
 

I did the following:
Sub test()
Dim cell As Range
Set cell = ActiveCell
ActiveCell.EntireRow.Delete
If cell Is Nothing Then
MsgBox "row deleted"
Else
MsgBox "cell variable is not nothing!"
End If
End Sub

The answer I got was "cell variable is not nothing!".

Question: how to check for the variable cell no longer existing? I know I
can do the following:

dim anyS as string
on error resume next
anys =""
anys = cell.address
on error goto 0
if anys ="" then
msgbox "cell is nothing"
end if

But there should be a cleaner way! Excel 2003

Bob

how to test if range object target deleted
 

Hi Bob,

Having assigned an object to an object to a variable, deleting the object
does not remove the variable pointer from memory and so the variable will
continue to be 'not nothing'. You can confirm that with objptr(cell).
I doubt there is a cleaner way to test if the object still exists other than
to attempt to refer to one of the variable's anticipated properties under an
error handler, though I suspect there is an API approach. I think the
method you posted is just fine!

Regards,
Peter T

"Bob Flanagan" wrote in message
. ..
I did the following:
Sub test()
Dim cell As Range
Set cell = ActiveCell
ActiveCell.EntireRow.Delete
If cell Is Nothing Then
MsgBox "row deleted"
Else
MsgBox "cell variable is not nothing!"
End If
End Sub

The answer I got was "cell variable is not nothing!".

Question: how to check for the variable cell no longer existing? I know
I can do the following:

dim anyS as string
on error resume next
anys =""
anys = cell.address
on error goto 0
if anys ="" then
msgbox "cell is nothing"
end if

But there should be a cleaner way! Excel 2003

Bob