While-loop crashes Excel-CodePudding

I've been experiencing problems with my Excel crashing when performing a simple while loop using Visual Basic. The idea is that I want to select and copy all the rows in a table that belong to a certain 'batch' which is defined in the first column

Sub Copy5Cells()
lColumn = Range("C7").End(xlToRight).Column
StartRow = ActiveCell.Row
Batch = Cells(StartRow, 1).Value
ControlBatch = Batch
MsgBox ControlBatch


Do Until (ControlBatch <> Batch)
nextRow = StartRow   1
ControlBatch = Cells(nextRow, 1).Value
Loop

num_rows = (nextRow - StartRow)
Range(ActiveCell, ActiveCell.Offset(num_rows, (lColumn - 2))).Copy
End Sub

here's an image of what the table looks like in Excel

CodePudding user response：

Each time the DO UNTIL loop goes round and starts again, it sets nextRow to StartRow 1. However, StartRow always has the same value, so nextRow will also always have the same value.

Just before the LOOP statement, add a line to increment StartRow:

Do Until (ControlBatch <> Batch)
    nextRow = StartRow   1
    ControlBatch = Cells(nextRow, 1).Value
    StartRow = StartRow   1
Loop

Alternatively, set the initial nextRow before the loop starts and increment it just before the loop:

nextRow = StartRow   1
Do Until (ControlBatch <> Batch)
    ControlBatch = Cells(nextRow, 1).Value
    nextRow = nextRow   1
Loop
num_rows = (nextRow - StartRow) - 1