On cell change - Clear Cells in that row

[Jayce72]

Hi,

I have found code that works that will delete certain cells if I change a key cell. e.g: If I change call B15 then cells C15: F15 get wiped - and the following works ok

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B15)) Is Nothing Then
Range("C15:F15").ClearContents
End If
End Sub

So how can I amend the code so it works on each line from 16 to say 100 - where it'll on wipe the data on the corresponding line?

Thanks

 

[FormR]

Hi, you could try like this.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, c As Range
Set r = Intersect(Target, Range("B16:B100"))
If Not r Is Nothing Then
    Application.EnableEvents = False
    For Each c In r.Cells
        If c.Value = "" Then c.Offset(, 1).Resize(, 4).ClearContents
    Next c
    Application.EnableEvents = True
End If
End Sub

 

[MARK858]

Maybe something like

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Row > 15 And Target.Row < 101 And Target.Cells.Count = 1 Then
        Application.EnableEvents = False

        If Not Intersect(Target, Columns(2)) Is Nothing Then
            Range(Cells(Target.Row, 3), Cells(Target.Row, 6)).ClearContents
        End If

        Application.EnableEvents = True
    End If
End Sub

 

[Jayce72]

Maybe something like

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Row > 15 And Target.Row < 101 And Target.Cells.Count = 1 Then
        Application.EnableEvents = False

        If Not Intersect(Target, Columns(2)) Is Nothing Then
            Range(Cells(Target.Row, 3), Cells(Target.Row, 6)).ClearContents
        End If

        Application.EnableEvents = True
    End If
End Sub

Thanks - perfect

 

[MARK858]

You're welcome

 

[FormR]

Hi, I don't know if you tried my suggestion or not, but one thing it will do differently is, if you change multiple cells in the range at once, it will still work - not sure if that might be important (or desirable) for you.

 

[MARK858]

Hi, I don't know if you tried my suggestion or not

Hi @FormR the code only clears the cells on the line if you change the Target cell/s to a blank cell, whereas the OP request was just for a change in the cell

 

[FormR]

the OP request was just for a change in the cell

Thanks Mark, I missed that. Here is my code amended for that.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, c As Range
Set r = Intersect(Target, Range("B16:B100"))
If Not r Is Nothing Then
    Application.EnableEvents = False
    For Each c In r.Cells
       c.Offset(, 1).Resize(, 4).ClearContents
    Next c
    Application.EnableEvents = True
End If
End Sub