Changing the ‘end’ value of a for loop

Topic

New Reply

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

Reply | Quote

Replies

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…)

Reply | Quote

You should loop backwards when you need to do this sort of thing:

Code:

For RowStart = finalrow to 3 step -1

Reply | Quote

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

Reply | Quote

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

Reply | Quote