VBA to clear contents of entire row if cell=#n/a

[NeoSez]

To be clear, I do not want to delete the entire row, I just want to clear the contents of the entire row if there is cell in that row that contains #n/a. Is this possible?

 

[alansidman]

Please show us a sample of what your data looks like. Use XL2BB to upload that sample.

 

[NeoSez]

Please show us a sample of what your data looks like. Use XL2BB to upload that sample.

So in this example, because it contains #N/A, I would like to just CLEAR row contents and leave it as a blank row, not DELETE the row. Can you help? Thank you.

Dog Name	Dog type	Type of request	Reference ID #	Client	Location	Cost	Account	CVV Code
#N/A	#N/A		#N/A	2	123456	500

 

[Peter_SSs]

1. Do those #N/A cells contain formulas or are they text values?

2. Is there likely to be any other error values on the sheet (eg #VALUE!, #DIV/0! etc)?

 

[NeoSez]

1. Do those #N/A cells contain formulas or are they text values?

2. Is there likely to be any other error values on the sheet (eg #VALUE!, #DIV/0! etc)?

Hello Peter. Please see answers to your questions below.
1. Yes, it contains a formula, but returns #N/A because there is not enough data provided for that cell to calculate, hence, the reason to clear the row.
2. Only #N/A rows will be cleared. It will not contain any other error values in the row.
Thank you.

 

[Peter_SSs]

In that case try this with a copy of your workbook.

Sub Clear_NA_Rows()
  ActiveSheet.UsedRange.SpecialCells(xlFormulas, xlErrors).EntireRow.ClearContents
End Sub

 

[Trixterz]

If its a formula, then look within the formula for an "#N/A" and change it to blank "". For example:

Look for #N/A:

=iferror("My Formula","#N/A")

Change to:

=iferror("My Formula","")

 

[NeoSez]

In that case try this with a copy of your workbook.

Sub Clear_NA_Rows()
  ActiveSheet.UsedRange.SpecialCells(xlFormulas, xlErrors).EntireRow.ClearContents
End Sub

Thank you Peter_SSs. That's exactly what I wanted. Works prefectly!!

 

[Peter_SSs]

Thank you Peter_SSs. That's exactly what I wanted. Works prefectly!!

You're welcome. Thanks for the follow-up.