# Thread: Changing the 'end' value of a for loop

1. ## Changing the 'end' value of a for loop

HI all,

Is it possible to change the ‘end’ value of a for loop whilst in the actual loop?

The reason I’m asking is that I’m looping through some cells in a spreadsheet and inserting rows depending on certain conditions. I’m calculating the ‘last row’ of the spreadsheet using this piece of script:

finalrow = xlSht3.Cells(xlApp.Rows.Count, 6).End(xlUp).Row

I’m then using this value in a for loop as shown. Because I’m inserting rows, I need to tell the loop to now stop at the ‘new’ final row, not the original one. The problem seems to be that the loop still stops at the original value and not my updated value. Any ideas?

Code:
```TotalColumnLength = 1
For RowStart = 3 To finalrow
If xlSht3.Cells(RowStart + 1, 2) = "" Then
TotalColumnLength = TotalColumnLength + 1
Else
If TotalColumnLength <> 3 Then
xlSht3.Cells(RowStart + 1, 2).EntireRow.Select
If TotalColumnLength = 1 Then
xlApp.Selection.Insert Shift:=xlDown
xlApp.Selection.Insert Shift:=xlDown
RowStart = RowStart + 2
finalrow = finalrow + 2
Else
xlApp.Selection.Insert Shift:=xlDown
RowStart = RowStart + 1
finalrow = finalrow + 1
End If
TotalColumnLength = 1
Else
TotalColumnLength = 1
End If
End If
Next RowStart```

2. RowStart and FinalRow are evaluated once by Excel at the start of the loop.
The RowStart and FinalRow values are not reevaluated during the loop.
You will get uncertain results by trying the change those values during the loop. Don't do it.

One thing you could do is use a counter inside the loop and exit the loop using "Exit For" when the counter reaches a specified value: Counter = Counter + 1
As it is not clear to me exactly what the criteria is for quiting the loop, I can't advise further.
'---
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(XL Companion add-in: compares, matches, counts, lists, finds, deletes...)

3. You should loop backwards when you need to do this sort of thing:
Code:
`For RowStart = finalrow to 3 step -1`

4. Or use:

Code:
```RowStart = 3
finalrow = xlSht3.Cells(xlApp.Rows.Count, 6).End(xlUp).Row
Do While RowStart <= finalrow
'Your code here; make sure to increment and recompute as needed
Loop```

5. Hi Jasonsas

Start in revsrese. That is from the bottom going up the list and as you add rows the bottom will expand but the top is always going to be located at Row 1.

Thninking outside the box is a good feeling some times.

Wassim

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•