0

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

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

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.