For Next loop problem

J

Jo

Hi
Is there a limited time a for next loop will run? (not
counted). The end of the data is marked by text "END".
This works fine for small amounts of data, but seems to
exit around 96 samples. If I then re-run it its fine and
exits correctly.
Thanks
Jo

On Error GoTo errhandler
Dim endofdata
For Each cell In Range("results")
Range("O7").Select
endofdata = ActiveCell
If endofdata = "END" Then GoTo errhandler
various stuff here (cells get deleted and moved up)
Next
errhandler:
Exit Sub
 
H

Harlan Grove

Is there a limited time a for next loop will run? (not
counted). The end of the data is marked by text "END".
This works fine for small amounts of data, but seems to
exit around 96 samples. If I then re-run it its fine and
exits correctly. ...
various stuff here (cells get deleted and moved up)
Next
Click to expand...
...

If you're deleting cells, better to iterate from bottom to top or right to left.
So don't use a For Each loop, use

Dim n As Long
':
For n = Range("Results").Rows.Count To 1 Step -1
':
 
B

Bob Phillips

Jo,

There certainly is no limit in that way. What determines it is the size of
the range that you are iterating through. I would suggest though that
bombing out of the loop the way that you do is not a good practice.

It is probably caused by deleting stuff as you go through, as this can play
havoc with the pointers. It could then work a second time as the first pass
has cleared the path so to speak.

In this instance I would suggest an approach like this


On Error GoTo errhandler
Dim cell As Range
Dim rng As Range
For Each cell In Range("results")
If cell.Value = "END" Then
Exit For
Else
If cell.Value = "a value to be deleted"
If rng Is Nothin g Then
Set rng = cell
Else
Set rng = Union(rng, cell)
End If
End If
Next
If Not rng Is Nothing Then
rng.Entirerow.Delete
End If

errhandler:
Exit Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top