Nested loop with varying number of iterations

Question

I have to loop through 1 to 5 to determine if I need to keep or delete the ID.

For example

• I loop through all the 1s, there are more than zero "yes" and I keep 1.
• Loop through all 2s, there's no "yes", so I delete both 2s.
• For the same reason, I keep all 3s, delete all 4s and keep all 5s.

How do I set up these loops with different lengths?

Below I used conditional formatting to highlight the first entry of each ID, but I'm not sure how to use it.

Answer 1

Assuming your data is kept in the following range A2:B16,

Sub KeepMyData()
    Dim rngData As Range
    Dim rngRow As Range
    Dim valuesToKeep() As Integer
    Dim iCounter As Integer
    Dim blnKeep As Boolean

    Set rngData = ThisWorkbook.Worksheets(1).Range("A2:B16")

'   Run through the loop to find the values to keep
    For Each rngRow In rngData.Rows
        If rngRow.Cells(1, 2) = "yes" Then
            ReDim Preserve valuesToKeep(0 To iCounter)
            valuesToKeep(iCounter) = rngRow.Cells(1, 1)
            iCounter = iCounter + 1
        End If
    Next        

'   Delete the unwanted values
    For Each rngRow In rngData.Rows
        blnKeep = False
    '   Check if the current value is inside the values to keep
        For iCounter = 0 To UBound(valuesToKeep)
            If rngRow.Cells(1, 1).Value = valuesToKeep(iCounter) Then
                blnKeep = True
                Exit For
            End If
        Next
        ' Use this if you want to delete the entire row
        ' If Not blnKeep Then rngRow.Delete xlUp
        ' Use this if you just want to clear the row
        If Not blnKeep Then rngRow.Clear
    Next

End Sub