Macro Debug Error (Deleting a row)

[spyldbrat]

Hello,

I want to delete all rows if column N reads "NULL". I was given the last two lines of this macro a while back from someone (I believe from this forum) and it has worked in the past. I applied this to my current macro but I am getting a debug error when I get to the last 2 lines. Can someone help me?

Dim UsdRws As Long
UsdRws = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("N2").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC[-6]=""Released"",RC[-2]=""PAID"",RC[-1]=""PAID""),""Null"",""Keep"")"
Range("n2:n" & UsdRws).FillDown
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("N:N").Replace What:="Null", Replacement:="#N/A", LookAt:=xlWhole, MatchCase:=False
Columns("N:N").SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete

 

[Fluff]

What error message do you get & which line is highlighted when you click debug?

 

[spyldbrat]

Run-Time Error 1004: No Cells Were Found.

When I open the debug, the last line is highlighted. It is also not changing the "Null" to "#N/A" which is probably why I am getting this error, but I am not sure why it s not changing it to #N/A.
I don't really need to change Null to N/A, but in the past, I have not been able to get this to run on other macro's I added it to without using NULL and #N/A

 

[Fluff]

Do you have the actual word Null in col N?

 

[spyldbrat]

Yes I do. I see Null and Keep in the column at the point I get the debug

 

[Fluff]

In an unused cell type
=Len(N4)
where N4 is a cell that contains "Null", what is the result?

 

[spyldbrat]

4

 

[Fluff]

Try

   Dim UsdRws As Long
   UsdRws = Cells.find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
   With Range("N2:N" & UsdRws)
      .FormulaR1C1 = _
         "=IF(AND(RC[-6]=""Released"",RC[-2]=""PAID"",RC[-1]=""PAID""),""Null"",""Keep"")"
      .Value = .Value
      .Replace What:="Null", Replacement:="#N/A", LookAt:=xlWhole, MatchCase:=False
      .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
   End With

The problem is that you are only converting N2 to a value, so the rest of the column is still a formula.