Extend Loop From Within ?

[Blewyn]

I'm trying to extend a loop according to data in a sheet, as follows :

For Loop = 1 to EndOfLoop
If Cells(Loop,1) = 1 Then
SelectString = (Loop+1) & ":" & (Loop+1)
Rows(Selectstring).Insert Shift:xldown
EndOfLoop = EndOfLoop + 1
End If
Next Loop

In the above, if column 1 contains anything but 1 we go to the next loop. If column 1 contains a 1 we insert a blank line and increase the loop variable by 1, to account for the fact that all the original data has been shifted down 1 line from the current point.

The problem is, it doesn't seem to be affecting the loop count (as if the loop lenght is set at the initial execution of the FOR statement and then cannot be altered. If I put

MsgBox(Loop)
MsgBox(EndOfLoop)

at the end of the code, I get Loop = original EndOfLoop and EndOfLoop = (original EndOfLoop+Number Of Blank Lines Inserted).

Is it not possible to dynamically alter a loop terminator variable from within a loop ? Do I have to use do...until ?

[mudraker]

As far as I know you can not alter the End loop number once you are inside the for loop

You could use a do - loop or use a For loop stepping backwards

This is how I would do it

Sub ffff()
Dim iLoop As Integer
Dim iEndOfLoop As Integer

iEndOfLoop = Cells(Rows.Count, "a").End(xlUp).Row
For iLoop = iEndOfLoop To 1 Step -1
If Cells(iLoop, 1) = 1 Then
Rows(iLoop + 1).Insert Shift:=xlDown
End If
Next iLoop
End Sub

[Blewyn]

You could use a do - loop or use a For loop stepping backwards

DOH ! <bangs head against desk in vain hope of restarting brain>

Thanks