If value of specific cell change then clear contents from that row only from which cell value change

user_9309149 0
Viewed 765

I am trying to build a code where, if there is change in cell then clear contents from that row only where value of cell changed. here is my code,

   Private Sub Worksheet_Change(ByVal Target As Range)

     Dim KeyCells As Range

     Set KeyCells = Range("I1:I209")

     If Not Application.Intersect(KeyCells, Range(Target.Address)) _
     Is Nothing Then

     If MsgBox("ARE YOU SURE, YOU WANT TO DELETE?", vbYesNo + vbQuestion) = vbNo Then Exit Sub
       'WHAT SHOULD BE CODE TO DELETE CONTENTS FROM THAT ROW FROM WHICH CELL VALUE CHANGED
     End If

    End Sub

Any help will be appreciated. Thank you.

excel-formula vba excel
2 Answers

Use EntireRow property of Range object to get Target row range:

 Target.EntireRow.ClearContents

while to clear only changed cells:

Target.ClearContents

and if you want to clear cells in columns B to, J to M and Q to S in same row as changed cell, then:

Intersect(Target.EntireRow, Range("B:D, J:M ,Q:S")).ClearContents

in all cases you should treat the above codes as Jeeped showed in his answer, i.e.:

  • make sure the clearing doesn't trig Worksheet_Change event in infinite loop by enclosing the clearing code beteeen Application.EnableEvents = False and Application.EnableEvents = True

  • make sure any possible error doesn't prevent form reaching Application.EnableEvents = True statement, by placing an On Error GoTo safe_exit statement before Application.EnableEvents = False and moving Application.EnableEvents = True right after a safe_exit: statement

user_93128920

Try,

Private Sub Worksheet_Change(ByVal Target As Range)

     If Not Intersect(Range("I1:I209"), Target) Is Nothing Then

        If MsgBox("ARE YOU SURE, YOU WANT TO DELETE?", vbYesNo + vbQuestion) <> vbYes Then
            Exit Sub
        Else
            On Error GoTo safe_exit
            Application.EnableEvents = False
            Dim t As Range
            For Each t In Intersect(Range("I1:I209"), Target)
                intersect(range("B:D,J:M,Q:S"), t.EntireRow).ClearContents
            Next t
     End If

safe_exit:
    Application.EnableEvents = true

End Sub
Admin1
Related