Delete N/A

[howard]

I would like to delete #N/A in Col U from row 2 onwards


I have written code to do this, but cannot get the macro to delete these

 Sub Delete_Unwanted_Data()

Sheets("Creditors Paid").Select

Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
 If Range("U" & i) = "#N/A" Then EntireRow.Delete
     
Next i
End Sub

It would be appreciated if someone could kindly assist me

 

[Norie]

Try this.

Sub Delete_Unwanted_Data()
Dim LR As Long, i As Long

    Sheets("Creditors Paid").Select

    LR = Range("A" & Rows.Count).End(xlUp).Row

    For i = LR To 2 Step -1
        If Range("U" & i) = "#N/A" Then Rows(i).Delete     
    Next i

End Sub

 

[Rick Rothstein]

You have formulas in Column U, correct? If so, and assuming #N/A is the only errors you have displayed in Column U, then give this macro a try...

Sub Delete_Unwanted_Data()
  Columns("U").SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
End Sub

 

[howard]

Hi Norie


Thanks for the help


I get type mismatch when running the code and the following is highlighted

 If Range("U" & i) = "#N/A" Then Rows(i).Delete

Kindly check & amend

 

[Rick Rothstein]

@howard,

Our messages crossed in the ether at about the same time, so you may not have noticed what I posted in Message #3 ... you might find it interesting.

 

[Norie]

Oops, missed an important part of the code.

Try this.

Sub Delete_Unwanted_Data()
Dim LR As Long, i As Long

    Sheets("Creditors Paid").Select

    LR = Range("A" & Rows.Count).End(xlUp).Row

    For i = LR To 2 Step -1
        If Range("U" & i).Text = "#N/A" Then Rows(i).Delete     
    Next i

End Sub