Delete Rows that contain a specific value VBA

Question

I'm looking to create a simple procedure which will search for the 'In Shout?' column, search for all '#N/A's' in this column and delete these rows.

When I run the below, it doesn't delete the rows and I can't figure out why. Can anyone see why?

Sub DeleteBadRows()

    Dim InShout As Long
    Dim NA As String
    
    NA = "#N/A"

    'Declaring year value of 1 month & 2 month
    'This is important to compare datasets from 2 months ago & last month
    Year_2M = Format(Date - 57, "YYYY")

    'Declaring month value of 1 month & 2 month
    'This is important to compare datasets from 2 months ago & last month
    Month_2M = Format(Date - 57, "MM")

    'This translates the current month from number to character format
    MonthChar_2 = MonthName(Month_2M, False)

    sheet = "MASTERFILE_" & Year_2M & Month_2M
    
    'setting string values so that we can identify open workbooks
    myFile = "Dataset"
    otherFile = "Monthly Reporting Tool"
    shoutFile = "Copy of Daily Shout"
    
        'if tool wb is open, declare it as MonthlyRepTool
    For Each wb In Application.Workbooks
        If wb.Name Like otherFile & "*" Then
           Set MonthlyRepTool = Workbooks(wb.Name)
        End If
    Next wb
    
        With MonthlyRepTool.Worksheets(sheet).Rows(1)
            Set e = .Find("In Shout?", LookIn:=xlValues)
            InShout = e.Column
        End With

    lastRow = MonthlyRepTool.Worksheets(sheet).Cells(Rows.count, "A").End(xlUp).Row

    For i = 2 To lastRow Step -1

        If MonthlyRepTool.Worksheets(sheet).Cells(i, InShout).value = NA Then
            MonthlyRepTool.Worksheets(sheet).Rows(i).EntireRow.Delete
        End If
        
    Next i

End Sub

Answer 1

Tested your code. Two things:

1. Instead of If MonthlyRepTool.Worksheets(sheet).Cells(i, InShout).value = NA Then
   use If Application.IsNa(MonthlyRepTool.Worksheets(Sheet).Cells(i, InShout).Value) Then

2. Instead of For i = 2 To lastRow Step -1
   use For i = lastRow To 2 Step -1