CastingDirector
New Member
- Joined
- Jun 10, 2014
- Messages
- 46
So simple I can't get it.
I need specific cells ("A1, A3, A5") to clear its contents after the (single) row is copied onto another sheet. Currently, I have it clearing the entire row (including a formula in A:3) when I only want these 3 cells to clear without removing the formula. Here Tiz:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
If Intersect(Target, Range("A:A")) = "No to Material" Then
Target.EntireRow.Copy Sheets("Non Avail").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
Target.EntireRow.ClearContents ' I would like to clear just 3 cells in this row, leaving the rest of the row as is
Can you fix this, please? Kind Regards

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
If Intersect(Target, Range("A:A")) = "No to Material" Then
Target.EntireRow.Copy Sheets("Non Avail").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
Target.EntireRow.ClearContents ' I would like to clear just 3 cells in this row, leaving the rest of the row as is
Can you fix this, please? Kind Regards