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

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
``````

## 1 Answer

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
``````
• Worked like a charm...thank you so much!! – RobACVVA Jul 13 '17 at 20:56
• Please mark as correct by clicking the check mark by the answer. – Scott Craner Jul 13 '17 at 20:58