Add specific rows to another array

Question

I have a code in which it goes through all the rows and looks to see if column I contains the word 'MISS' and adds that to the array. I now want it to know those rows are stored into it, and instead store the rows that aren't already in an array into a different array. My current code is:

Sub tester()

max_targets = 6
Dim RowsToMiss(1 To 6)

lRow = 2
Do Until Sheets("Result").Cells(lRow, 1) = ""
    If Sheets("Result").Cells(lRow, 9) = "MISS" Then
        RowsToMiss(Sheets("Result").Cells(lRow, 4)) = lRow
    End If
    lRow = lRow + 1
Loop

lRow = 2
Do Until Sheets("Result").Cells(lRow, 1) = ""
    If RowsToMiss(Sheets("Result").Cells(lRow, 4)) <> lRow Then
        'read the row in!
        Else
    End If
    lRow = lRow + 1
Loop
End Sub

Answer 1

If you need to add values to a collection dynamically, VBA provides the type Collection, which is a better than Array, because its size can be increased dynamically. Thus, in your case, you need to loop through the second column, check the values in the cells and add them to one of the two available collections:

Option Explicit

Sub TestMe()

    Dim withMiss        As New Collection
    Dim withoutMiss     As New Collection
    Dim currentRow      As Long

    currentRow = 2
    With Worksheets(1)
        Do Until .Cells(currentRow, 1) = vbNullString
            If UCase(.Cells(currentRow, 1)) = "MISS" Then
                withMiss.Add currentRow
            Else
                withoutMiss.Add currentRow
            End If
        currentRow = currentRow + 1
        Loop
    End With

    Dim cnt As Long

    Debug.Print "Rows with MISS"
    For cnt = 1 To withMiss.Count
        Debug.Print withMiss.item(cnt)
    Next cnt

    Debug.Print "Rows without MISS"
    For cnt = 1 To withoutMiss.Count
        Debug.Print withoutMiss.item(cnt)
    Next cnt

End Sub

This is something that you would get in the immediate window CTRL+G, if the 2,3,4 & 8 cell of the 1. column are with the word "MISS":

Rows with MISS
 2 
 3 
 4 
 8 
Rows without MISS
 5 
 6 
 7 

Answer 2

Like @brainac I'm unsure what you are asking, but have taken a punt. This creates another array which stores the row numbers which do not contain MISS.

Sub tester()

Dim max_targets As Long, lRow As Long, i As Long

max_targets = 6
Dim RowsToMiss(1 To 6)
Dim RowsToHit()

For lRow = 2 To Sheets("Result").Cells(Rows.Count, 1).End(xlUp).Row
    If Sheets("Result").Cells(lRow, 9) = "MISS" Then
        RowsToMiss(Sheets("Result").Cells(lRow, 4)) = lRow
    Else
        i = i + 1
        ReDim Preserve RowsToHit(1 To i)
        RowsToHit(i) = lRow
    End If
Next lRow

End Sub