Reputation: 13
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
Upvotes: 1
Views: 100
Reputation: 7344
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
Upvotes: 2