Update dynamic range in For...Loop?

VoG

Legend

Joined

Jun 19, 2002

Messages

63,650

• Aug 31, 2010

• #2

Try

Code:

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

 

Upvote 0

Joe4

MrExcel MVP, Junior Admin

Joined

Aug 1, 2002

Messages

74,669

Office Version

1. 365

Platform

1. Windows

• Aug 31, 2010

• #3

Try looping through your range backwards, i.e.

Code:

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

 

Upvote 0

B

BigDelGooner

Board Regular

Joined

Aug 17, 2009

Messages

197

• Aug 31, 2010

• #4

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

 

Upvote 0

VoG

Legend

Joined

Jun 19, 2002

Messages

63,650

• Aug 31, 2010

• #5

Try

Code:

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

 

Last edited: Aug 31, 2010

Upvote 0

Joe4

MrExcel MVP, Junior Admin

Joined

Aug 1, 2002

Messages

74,669

Office Version

1. 365

Platform

1. Windows

• Aug 31, 2010

• #6

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

Click to expand...

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

 

Upvote 0

Ruddles

Well-known Member

Joined

Aug 24, 2010

Messages

5,851

Office Version

1. 365

Platform

1. Windows

• Aug 31, 2010

• #7

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

Code:

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

 

Last edited: Aug 31, 2010

Upvote 0

B

BigDelGooner

Board Regular

Joined

Aug 17, 2009

Messages

197

• Aug 31, 2010

• #8

Joe4 said:

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.

Click to expand...

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

 

Upvote 0

Ruddles

Well-known Member

Joined

Aug 24, 2010

Messages

5,851

Office Version

1. 365

Platform

1. Windows

• Aug 31, 2010

• #9

Oops, I think I meant:-

Code:

do until c>LR

 

Upvote 0

B

BigDelGooner

Board Regular

Joined

Aug 17, 2009

Messages

197

• Aug 31, 2010

• #10

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.

 

Upvote 0