Creating timestamp if cell range changes

[0rtli]

Hi guys,
So I need some help solving the issues.
I have data range from A2:Q and if in this range cell is empty R also should be empty, if A2:Q were filled with something, I would like to have a timestamp in column R.
Besides this, data in A2:Q can be copy&pasted so I found an old thread where VBA code works perfectly (without cleaning the timestamp when the cell is empty) but it can't handle when I'm bulk copy&pasting more than 1 row at the same time.

Old thread:

Creating a timestamp for changes made across a range of cells in a row

Hi! I want to track changes I have made on my worksheet by having a time stamp against each row. I have data from columns C to AX. I would like to have a timestamp in Column AY (only date would also work), if any value changes in any cell between C and AX. I found a couple of VBA codes which...

www.mrexcel.com

 

[0rtli]

Your current code is only set-up to check one cell in one column. That is easy.
Trying to checking multiple columns in multiple rows is a bit trickier.

Try something like this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    Dim r As Long
    Dim rng2 As Range
 
'   See if any cells in watched range updated
    Set rng = Intersect(Columns("A:Q"), Target)
    If rng Is Nothing Then Exit Sub
 
    Application.EnableEvents = False
 
'   Loop through all cells in range
    For Each cell In rng
'       Get row of update
        r = cell.Row
'       Check to make show update is not in row 1
        If r > 1 Then
'           Build range to check
            Set rng2 = Range(Cells(r, "A"), Cells(r, "Q"))
'           Check to see if any cells in row have a value
            If Application.WorksheetFunction.Count(rng2) > 0 Then
                Cells(cell.Row, "R").Value = Now()
            Else
'               Clear timestamp if all blank
                Cells(cell.Row, "R").ClearContents
            End If
        End If
    Next cell

    Application.EnableEvents = True
 
End Sub

Note that if you are updating multiple columns and rows at once, the code will loop more than it needs to (as it loops through each cell), though it will still work.

Any idea why the timestamp appears only when I enter numbers in range A:Q while using letters the timestamp cell is empty?

 

[Joe4]

Whoops, I meant to use COUNTA, not COUNT.
COUNT only counts numbers. COUNTA will count anything.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    Dim r As Long
    Dim rng2 As Range
  
'   See if any cells in watched range updated
    Set rng = Intersect(Columns("A:Q"), Target)
    If rng Is Nothing Then Exit Sub
  
    Application.EnableEvents = False
  
'   Loop through all cells in range
    For Each cell In rng
'       Get row of update
        r = cell.Row
'       Check to make show update is not in row 1
        If r > 1 Then
'           Build range to check
            Set rng2 = Range(Cells(r, "A"), Cells(r, "Q"))
'           Check to see if any cells in row have a value
            If Application.WorksheetFunction.CountA(rng2) > 0 Then
                Cells(cell.Row, "R").Value = Now()
            Else
'               Clear timestamp if all blank
                Cells(cell.Row, "R").ClearContents
            End If
        End If
    Next cell

    Application.EnableEvents = True
  
End Sub