VBA - Make same formulas into a single one

[The Godfather]

Hi,

I have the following formula below, where a single task is done for 10 different rows (stars with row 12 and ends with row 21).
I typed the code for each row. Is it possible to do it just with a single code? (I tried ranging the cells - ie ranging "I12:J12"- but it crashed)

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Range("I12"), Range(Target.Address)) Is Nothing Then
Range("J12").Value = ""
Range("K12").Value = ""
End If

If Not Intersect(Range("I13"), Range(Target.Address)) Is Nothing Then
Range("J13").Value = ""
Range("K13").Value = ""
End If

If Not Intersect(Range("I14"), Range(Target.Address)) Is Nothing Then
Range("J14").Value = ""
Range("K14").Value = ""
End If

If Not Intersect(Range("I15"), Range(Target.Address)) Is Nothing Then
Range("J15").Value = ""
Range("K15").Value = ""
End If

If Not Intersect(Range("I16"), Range(Target.Address)) Is Nothing Then
Range("J16").Value = ""
Range("K16").Value = ""
End If

If Not Intersect(Range("I17"), Range(Target.Address)) Is Nothing Then
Range("J17").Value = ""
Range("K17").Value = ""
End If

If Not Intersect(Range("I18"), Range(Target.Address)) Is Nothing Then
Range("J18").Value = ""
Range("K18").Value = ""
End If

If Not Intersect(Range("I19"), Range(Target.Address)) Is Nothing Then
Range("J19").Value = ""
Range("K19").Value = ""
End If

If Not Intersect(Range("I20"), Range(Target.Address)) Is Nothing Then
Range("J20").Value = ""
Range("K20").Value = ""
End If

If Not Intersect(Range("I21"), Range(Target.Address)) Is Nothing Then
Range("J21").Value = ""
Range("K21").Value = ""
End If

End Sub

 

[grabrail]

Im not an expert but I have been playing with a similar thing recently, can you try this and see if it works for you?

Ive only done the first 5, you can add more into the ranges using commas until you have all, if it works.

If Not Intersect(Range("I12, I13, I14, I15, I16"), Range(Target.Address)) Is Nothing Then
Range("J12, K12, J13, K13, j14, k14, j15, k15").Value = ""
End If

 

[Michael M]

MAybe this way

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long
For r = 12 To 21
If Not Intersect(Range("I" & r), Range(Target.Address)) Is Nothing Then
Range("J" & r).Value = ""
Range("K" & r).Value = ""
End If
Next r
End Sub

 

[The Godfather]

Im not an expert but I have been playing with a similar thing recently, can you try this and see if it works for you?

Ive only done the first 5, you can add more into the ranges using commas until you have all, if it works.

If Not Intersect(Range("I12, I13, I14, I15, I16"), Range(Target.Address)) Is Nothing Then
Range("J12, K12, J13, K13, j14, k14, j15, k15").Value = ""
End If

Thank you grabrail, I tried your solution. Infortunately if I change only one cell in I column, all cells in J and K columns become empty.

 

[The Godfather]

MAybe this way

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long
For r = 12 To 21
If Not Intersect(Range("I" & r), Range(Target.Address)) Is Nothing Then
Range("J" & r).Value = ""
Range("K" & r).Value = ""
End If
Next r
End Sub

Thank you Michael M, it worked.

 

[Michael M]

Glad to help..