When Deleting a Range, Range Updates Every Cell I\'m using the code below. It lo

Question

When Deleting a Range, Range Updates Every Cell

I'm using the code below. It looks at the last cell that changed in the range B3:B100 to I3:I100, then it updates Column A by retrieving the column number of the changed cell, subtracting 1 and then negating that number. A problem arises when I delete a row. If I delete a whole row, all of the cells update with the "Now()" date. I want to be able to delete an entire row or range without it updating every single cell, also when I delete the row, I want A not to update I want it completely gone.

In Rows #3 and #4, when I write "X" under completed, it updates in Column A. However, look what happens when I try to delete rows #6 to #10 (THAT IS THE PROBLEM).

Private Sub Worksheet_Change(ByVal Target As Range)

Dim R1 As Range

Dim R2 As Range

Dim InRange As Boolean

Set R1 = Range(Target.Address)

Set R2 = Range("B3:B100", "I3:I100")

Set InterSectRange = Application. Intersect(R1, R2)

InRange = Not InterSectRange Is Nothing

Set InterSectRange = Nothing

If InRange = True Then

R1.Offset(0, -(R1.Column - 1)).Value=Now()

End If

Set R1 = Nothing

Set R1 = Nothing

Any suggestion????

Explanation / Answer

Try this version:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim R1 As Range
    Dim R2 As Range
    Set R2 = Intersect(Range("B3:I100"), Target)
    If Not R2 Is Nothing = True Then
        Application.EnableEvents = False
        For Each R1 In R2.Rows
            Range("A" & R1.Row).Value = Now
        Next R1
        Application.EnableEvents = True
    End If
End Sub

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.