How to repeat a loop until the end of my spreadsheet?

Question

I need help on how to repeat this loop below until the end of my spreadsheet. I built 2 loops below to work on the first 50 rows and it works fine. However, I have another 7000 rows that I need to apply both of these loops to (the loops should work on 50 rows at a time).

The first loop assigns a value of 1 or 0 in column O depending on the value in column K.

The second loop then adds 1 to each cell in column O that doesn't contain 0 until the sum of the 50 cell array in column O equals 64.

Sub assign_values()
    
        For i = 2 To 51
        
            If Cells(i, "K").Value > 0 Then
               
               Cells(i, "O").Value = 1
               Else
               Cells(i, "O").Value = 0
               
          End If
          
      Next i
               
        For i = 2 To 51
        
            If Application.WorksheetFunction.Sum(Range("O:O")) = 64 Then Exit Sub
            Cells(i, "O").Value = Cells(i, "O").Value + 1
    
        Next i
    
    End Sub

Again, this loop works fine for the first 50 rows. However, I cannot seem to figure out how to get this loop to apply to the next 7000 rows.

You guys have been a great help to me and I appreciate all of your answers.

Thanks,

G

Answer 1

Sub assign_values()

    Const BLOCK_SZ As Long = 50
    Dim rng, c, tot

    Set rng = Range("O2").Resize(BLOCK_SZ, 1) '<< first block of 50 rows

    'keep going while there's content in Col K (you may need to adjust
    '  where you check for content)
    Do While Application.CountA(rng.Offset(0, -4)) > 0

        For Each c In rng.Cells
            c.Value = IIf(c.Offset(0, -4).Value > 0, 1, 0) 'based on ColK
        Next c

        tot = Application.Sum(rng) '<< only need this once, then keep count
        For Each c In rng.Cells
            If tot < 64 Then
                c.Value = c.Value + 1
                tot = tot + 1
            Else
                Exit For
            End If
        Next c

        Set rng = rng.Offset(BLOCK_SZ, 0) '<< next block of cells
    Loop

End Sub