Change Event for Horizontal Range

[Victor Moss]

Hi Guys,

I have 2 separate ranges, A5:G6000 & N5:Q6000, when I clear contents of all cells in a row in one range, not only some, I'd like the cells in the same row in the other range to be cleared. I know it's a Worksheet_Change event as I have a couple in my Sheet already, but I don't know how to code this one.

Any help would be appreciated.

 

[maabadi]

How about:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long, Cr1 As Long, Cr2 As Long
If Not Intersect(Target, Range("A5:G6000")) Is Nothing Then
Cr1 = Application.WorksheetFunction.Count(Target)
If Cr1 = 0 Then Range("N5:Q6000").ClearContents
End If
If Not Intersect(Target, Range("N5:Q6000")) Is Nothing Then
Cr2 = Application.WorksheetFunction.Count(Target)
If Cr2 = 0 Then Range("A5:G6000").ClearContents
End If
End Sub

 

[Victor Moss]

Thank you Maabadi,

If Cr1 = 0 Then Range("N5:Q6000").ClearContents

I don't want to clear the entire range, I just want to clear cells in the target row of Range("N5:Q6000")

So If Range("A6:G6") are cleared, then Range("N6:Q6") must be cleared, if that explains it a bit more clearly.

 

[maabadi]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long, Cr1 As Long
If Not Intersect(Target, Range("A5:G6000")) Is Nothing Then
Application.EnableEvents = False
Debug.Print Target.Rows.Count
Cr1 = Application.WorksheetFunction.Count(Target)
If Cr1 = 0 Then Range("N" & Target.Row & ":Q" & Target.Row + Target.Rows.Count - 1).ClearContents
Application.EnableEvents = True
End If
End Sub

 

[Victor Moss]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long, Cr1 As Long
If Not Intersect(Target, Range("A5:G6000")) Is Nothing Then
Application.EnableEvents = False
Debug.Print Target.Rows.Count
Cr1 = Application.WorksheetFunction.Count(Target)
If Cr1 = 0 Then Range("N" & Target.Row & ":Q" & Target.Row + Target.Rows.Count - 1).ClearContents
Application.EnableEvents = True
End If
End Sub

Perfect, thank you very much.

 

[maabadi]

You're Welcome & Thanks for Feedback.

 

[Victor Moss]

You're Welcome & Thanks for Feedback.

Apologies, I know we concluded this, but I was as well looking to do this:

If Not Application.Intersect(Target, Range("A5:Q6000")) Is Nothing Then
If Cells(Target.Row, 1) = "" And Cells(Target.Row, 2) = "" And Cells(Target.Row, 3) = "" And Cells(Target.Row, 4) = "" And Cells(Target.Row, 5) = "" And Cells(Target.Row, 6) = "" Then
Application.EnableEvents = False
Cells(Target.Row, 14).ClearContents
Cells(Target.Row, 15).ClearContents
Cells(Target.Row, 16).ClearContents
Cells(Target.Row, 17).ClearContents
Application.EnableEvents = True
End If
End If

The only problem I have here is that if more than 1 row is cleared, only the first row is affected by the Change.

 

[maabadi]

If you want Use your code change it to this:

If Not Application.Intersect(Target, Range("A5:Q6000")) Is Nothing Then
If Cells(Target.Row, 1) = "" And Cells(Target.Row, 2) = "" And Cells(Target.Row, 3) = "" And Cells(Target.Row, 4) = "" And Cells(Target.Row, 5) = "" And Cells(Target.Row, 6) = "" Then
Application.EnableEvents = False
Range(Cells(Target.Row, 14),  Cells(Target.Row + Target.Rows.Count - 1, 17).ClearContents
Application.EnableEvents = True
End If
End If

 

[Victor Moss]

If you want Use your code change it to this:

If Not Application.Intersect(Target, Range("A5:Q6000")) Is Nothing Then
If Cells(Target.Row, 1) = "" And Cells(Target.Row, 2) = "" And Cells(Target.Row, 3) = "" And Cells(Target.Row, 4) = "" And Cells(Target.Row, 5) = "" And Cells(Target.Row, 6) = "" Then
Application.EnableEvents = False
Range(Cells(Target.Row, 14),  Cells(Target.Row + Target.Rows.Count - 1, 17).ClearContents
Application.EnableEvents = True
End If
End If

Excellent. Thank you very much. I wish I had the same expertise as you.

 

[maabadi]

You're Welcome.