VBA - Clear contents of a row, dependent on a cell's value

[vvilko]

Hi Guys.

I'm looking at creating a VBA code to look for the value in 'J' Column to change to "Yes". When it changes, I'd like it to clear all values to the Right (K to AH) in that particular row (leaving formulas in "Y", "AA" and "AC").
it would also have to check every row in the workbook.
I've been trying different things for about 4 hours now, and I've only managed to get it to clear a single row...

I hope you can help me out.

*Sorry, I tried to use XL2BB but I think my IT have disabled Microsoft Add-Ins.*

thanks in advance.
vvilko

 

[Akuini]

Hi, @vvilko
Try this:
This is an Event Procedure, you need to put it in the code module of the sheet in question (say sheet1). This is how:
Copy the code > open sheet1 > right click sheet1 tab > select View Code > paste the code.

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo skip:
If Target.Cells.CountLarge > 1 Then Exit Sub
    If Not Intersect(Target, Range("J:J")) Is Nothing Then
        If Target.Value = "Yes" Then
        Dim i As Long
        Application.EnableEvents = False
            i = Target.Row
            Range("K" & i & ":AH" & i).SpecialCells(xlCellTypeConstants).ClearContents
        Application.EnableEvents = True
        End If
    End If

Exit Sub
skip:
Application.EnableEvents = True
MsgBox "Error number " & Err.Number & " : " & Err.Description
End Sub

 

[vvilko]

That works amazingly! Thank you so much for the help. I'm slowly learning, but some things go over my head.

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.