excel vba what happens to range objects if user deletes cells code example

Example: 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