VBA to clear specific cells in a single row

[CastingDirector]

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

 

[WarPigl3t]

What is the formula in A3?

 

[CastingDirector]

Thanks for asking:
=IFERROR(VLOOKUP(G11,Resource!$A:$B,2,0),"")

 

[WarPigl3t]

Here's the problem. Let's say that you cleared cells A1, A2, and A3. Then you inserted that formula into A3 again. When you inset the formula, it will not be clear because you have a value in G11. It's just going to lookup the value again and your cell will not be clear again. So now you need to decide what you want to do about this before I can help. One solution I can think of is that you can write code that looks at cell A1 and if there is a value in A1 that is not blank, then it can insert that formula into A3.

IF Range("A1").value <> "" Then
Range("A3").value = "[COLOR=#333333]=IFERROR(VLOOKUP(G11,Resource!$A:$B,2,0),"""")"
[/COLOR]End if

Or you could just write a formula saying
=IF(A1<>"", IFERROR(VLOOKUP(G11,Resource!$A:$B,2,0),""),"")
Just don't clear A3 in your code.

 

[CastingDirector]

Thanks so much! I also thought this might work in VBA:
Target.EntireRow.Cells.SpecialCells(xlConstants).ClearContents
Both are quite viable.
Again my thanks and best regards!