Update dynamic range in For...Loop?

[BigDelGooner]

Hi guys

I've simplified the code but having trouble with the following...

for c = 3 to Range("A65536").end(xlup).row
if cells(c,2).value = true then
rows(c).insert
end if
next

Basically because I am inserting row during the procedure the loop only remembers the initial last row rather than continually checking the range("A65536").end(xlup).row after each loop.

I have tried using a named range (i.e. End_Row) and incorporating this into the loop but this doesnt work either.

Any ideas?

 

[VoG]

Try

LR = Cells(Rows.Count, 2).End(xlUp).Row
For c = LR To 3 Step -1
    If Cells(c, 2).Value = True Then Rows(c).Insert
Next c

 

[Joe4]

Try looping through your range backwards, i.e.

for c=Range("A65536").end(xlup).row to 3 Step -1
if cells(c,2).value = true then
rows(c).insert
end if
next

 

[BigDelGooner]

To cut a long story short is there definitely not a way to do this by looping forwards rather than backwards (it basically helps avoid additional code due to table I am working with).

Thanks for the quick response guys

 

[VoG]

Try

Dim LR As Long, r As Range
LR = Cells(Rows.Count, 2).End(xlUp).Row
For c = 3 To LR
    If Cells(c, 2).Value = True Then
        If r Is Nothing Then
            Set r = Cells(c, 2)
        Else
            Set r = Union(r, Cells(c, 2))
        End If
    End If
Next c
If Not r Is Nothing Then r.EntireRow.Insert

 

[Joe4]

Whenever you are changing your range size by adding or deleting rows, it usually works better to loop through your range backwards, as you avoid missing rows or hitting a row twice as the range size changes.

What do you mean by?

it basically helps avoid additional code due to table I am working with

Can you explain that? I am not sure how you loop through the range would have any impact on that.

 

[Ruddles]

Use VoG's LR suggestion, work forwards, but increment LR each time you insert a row:-

LR= Range("A65536").end(xlup).row
c = 3
do until c>=LR
  if cells(c,2).value = true then
    rows(c).insert
    c=c+1      ' skip the new blank row if required
    LR=LR+1    ' move the Last Row pointer
  end if 
  c=c+1
loop

 

[BigDelGooner]

Whenever you are changing your range size by adding or deleting rows, it usually works better to loop through your range backwards, as you avoid missing rows or hitting a row twice as the range size changes.

What do you mean by?

Can you explain that? I am not sure how you loop through the range would have any impact on that.

Without waffling its quite tough to explain but here we go..

When I loop from the first row I automatically know what the start row is, when I get to the point where want to insert a row I am inserting a sum worksheetfunction from start row (that I know automatically) and the end row (the row I have just reached). Because I have located the end row when I insert the column I can easily identify the next start row for my next subtotal.

Looping backwards would be fine if I wanted the subtotal at the top of the range (because I could use the above method in reverse) but i'd really like it at the bottom...

Thanks again for your help all

 

[Ruddles]

Oops, I think I meant:-

do until c>LR

 

[BigDelGooner]

Ok so i've switched it from a for...next to a do...loop and extending the range dynamically seems to work there. Strange that it you are not able to range on a for...next loop but oh well.

Thank you very much for your help all.