excel vba what happens to range objects if user deletes cells
'Your code will raise a 'Runtime error 424: Object required'
'if a user deletes a range that your code has an open
'object reference to:
Set r = [a1]
Stop
'Now delete the first row and continue...
MsgBox r '<--displays: Runtime error 424: Object required
'To avoid this problem, use the following VBA function to
'test the validity of a range before accessing it:
Function InvalidRangeReference(r As Range) As Boolean
On Error Resume Next
If r.Count = 0 Then InvalidRangeReference = Err
End Function
'--------------------------------------------------------------------
Set r = [a1]
Stop
'Now delete the first row and continue...
If InvalidRangeReference(r) Then Set r = [a1]
'Now you have a brand new reference to A1.
MsgBox r '<--works perfectly