Suspend Code When Row is Deleted

[belmontry]

I am running the following to automatically date stamp a range of data when anything in its respective row is modified:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A2:AA7000")) Is Nothing Then Exit Sub
    
    Application.EnableEvents = False
    Range("A" & Target.Row) = Date
    Application.EnableEvents = True
    
End Sub

However, I often need to delete entire rows from the sheet. In doing so, the code effectively date stamps the sequential row that was previously deleted, although I have not intentionally input any data. Example: If I want to delete a full row of data in Sheet Row C, I delete the selected Row C, but the subsequent new Row C (previously Row D) is date stamped since its contents were modified to the new Row C locations.

I don’t know what to do to prevent the date stamp from running if, and only if, a row of data is deleted from the spreadsheet.

Thanks

 

[JamesW]

Hi,

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Intersect(Target, Range("A2:AA7000")) Is Nothing Then Exit Sub
    
    Application.EnableEvents = False
    Range("A" & Target.Row) = Date
    Application.EnableEvents = True
    
End Sub

Assuming that you will only ever enter data into one cell at a time.

 

[Jonmo1]

Welcome to the board..

Try this as the first line in the code..

If Target.Columns.Count = Columns.Count Then Exit Sub

Hope that helps

 

[belmontry]

Excellent, that does it.

Thanks.