Error while looping through column and deleting specific values

[Brock_Hardchest]

Hello,

In the below code I am trying to delete cells that say Inactive in column G, then I create a new column B filled with Vlookup values some of which return errors as "#N/A". The last part is identical to the earlier column G code but is supposed to delete cells with #N/A in column B. Running this produces a Type Mis-Match error for the line: If ws.Range("B" & k).Value = "#N/A" Then

This line works fine in the earlier code but fails the second time. Any help is appreciated!

Dim xrow As Long
Dim k As Long
Dim ws As Worksheet




Application.ScreenUpdating = False


Set ws = ActiveSheet


    For k = ws.Range("G" & Rows.Count).End(xlUp).Row To 2 Step -1
        If ws.Range("G" & k).Value = "Inactive" Then
            Rows(k).EntireRow.Delete
        End If
        
    
    Next k




xrow = Cells(Rows.Count, 1).End(xlUp).Row


Columns("B:B").Select
    Selection.Insert Shift:=xlToRight
    Range("B2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-1],'[Copy of Candlestick Product Crosswalk.xlsx]Crosswalk from Dallas'!R2C1:R188C3,3,FALSE)"
    Range("B2").Select
    Selection.AutoFill Destination:=Range("B2:B" & xrow)


    For k = ws.Range("B" & Rows.Count).End(xlUp).Row To 2 Step -1
        If ws.Range("B" & k).Value = "#N/A" Then
            Rows(k).EntireRow.Delete
        End If
        
    
    Next k

 

[iliace]

Maybe try like this instead:

  For k = ws.Range("B" & Rows.Count).End(xlUp).Row To 2 Step -1
    If IsError(ws.Range("B" & k).Value) Then
      Rows(k).EntireRow.Delete
    End If
  Next k

 

[Fluff]

Another option

On Error Resume Next
Range("B:B").SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
On Error GoTo 0

 

[Brock_Hardchest]

Both solutions work, thanks for the help!

 

[Fluff]

Glad we could help & thanks for the feedback