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:

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

    I want it to be

    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

    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.