# Adding #N/A to the end of IF then formula and then deleting ALL #N/As found

• Hello,

I have a worksheet that I have to delete rows where data isn't found via an IF formula. Unfortunately I get this worksheet weekly from an automated program and the IF formula is not coded to spit out an error if false.

This is the current formula:

Code
1. =IF(ISTEXT(\$D16),VLOOKUP(\$B16,Data!\$A\$1:\$CA\$498,11,FALSE),"")

I want it to be

Code
1. =IF(ISTEXT(\$D16),VLOOKUP(\$B16,Data!\$A\$1:\$CA\$498,11,FALSE),"#N/A")

Basically I want to be able to add the #N/A to the end of the forumla in a certain range say C1:C21.

I am doing this because I believe it is the best way to delete empty lines so I am using this code to delete the lines with #N/A

Code
1. Last = Cells(Rows.Count, "D").End(xlUp).Row
2. For i = Last To 1 Step -1
3. If (Cells(i, "D").Text) = "#N/A" Then
4. Cells(i, "A").EntireRow.Delete
5. End If
6. Next i
7. End Sub

Is there a better way to do this? How would I make the VBA only look in D1:D21 instead of the entire worksheet.

Last question: What would be the best way to resize the lines without #N/A should I do that before or after I delete the #N/A lines.

Currently the way I am doing all of this is a super simple macro and referencing specific rows. The problem is the number of rows with data in them may change in the future.

THANK YOU SO MUCH!

• Re: Adding #N/A to the end of IF then formula and then deleting ALL #N/As found

Hi

Welcome to board !!!

To delete #N/A rows, try

[vb] Dim r As Range

On Error Resume Next
Set r = Range("D1:D21").SpecialCells(xlCellTypeFormulas, 16)

If Not r Is Nothing Then r.EntireRow.Delete[/vb]