Deleting rows in one sheet based on looking up value on another sheet

Question

I have an Excel file with raw data on one sheet (Forecast Data) and a list of values on another sheet (NonNSX). I am trying to write code that will cycle through Column D on Data, and if it finds a value list on NonNSX, delete the entire row on data.

I need it to check all rows on Data for the first nonNSX value, then go back to the top of Data, and check for the 2nd value on nonNSX, and loop all of this until done. In data there are often multiple duplicates of the same value on NSX, and I need to delete them all.

The code below works but it only deletes ONE of the rows on Data for each value each time I run the code. Any ideas? Note: the "d=d-1" inside the IF is to adjust the row number to look at next if a row is actually deleted)

Here is the code:

Sub Remove()

    Set nsx = Sheets("NonNSX")
    Set fc = Sheets("Forecast Data")
    Dim n As Integer
    Dim d As Integer
    Dim r As Integer
    n = 1
    d = 2
    r = 1
    NumRows = fc.Range("D2", fc.Range("D2").End(xlDown)).Rows.Count

    Do Until IsEmpty(nsx.Range("A" & n))
        For r = 1 To NumRows
            If nsx.Range("A" & n) = fc.Range("D" & d) Then
                fc.Range("D" & d).EntireRow.Delete
                Exit For
                d = d - 1
            End If
            d = d + 1
        Next r
        d = 2
        n = n + 1
    Loop

End Sub

Answer 1

We can replace one of the loops with a Match.

We also want to loop backwards from the bottom up.

Sub Remove()
Dim nsx As Worksheet
Dim fc As Worksheet

Set nsx = Sheets("NonNSX")
Set fc = Sheets("Forecast Data")

Dim lookUp As Range
Dim n As Long
Dim d As Long

NumRows = fc.Range("D1", fc.Range("D2").End(xlDown)).Rows.Count

Set lookUp = nsx.Range("A1", nsx.Range("A1").End(xlDown))

For n = NumRows To 2 Step -1
    d = 0
    On Error Resume Next
        d = Application.WorksheetFunction.Match(fc.Range("D" & n), lookUp, 0)
    On Error GoTo 0
    If d > 0 Then
        fc.Rows(n).Delete
    End If
Next n



End Sub