# For Loop exits too soon?

#### 2020914

##### New Member
Hello -

I have a For Loop that is exiting early, and I cannot figure out why. Hoping maybe someone can see a flaw in my logic below.

I have data in columns A:Z. In column 27 (AA), I have a formula which determines if that row is the first occurrence of a PO Number located in the preceding column. If this is the first occurrence of that PO Number, my formula returns a value of "Y" and then the code below looks for those "Y" values and inserts a row above.

My For Loop starts at i = 2 because row 1 is headers. I'm determining the last row which has data and assigning that to the Last_Row variable. From there, the loop executes, and any time it finds a "Y" it inserts a row, and then I increment both my 'i' and 'Last_Row' variables by 1.

For some reason, my For Loop is exiting at row 1334 even though my Last_Row variable reaches 1444 as proven by the Debug statement. The code appears to be working correctly up until the point where it exits the loop, but because it exists early it's missing some "Y" values. I'm at a loss and would appreciate any help! Thanks in advance!

VBA Code:
``````Sub Test()

With Sheet_OG

Dim i As Long
Dim Last_Row As Long
Last_Row = .Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To Last_Row

If Cells(i, 27) = "Y" Then
Rows(i).Insert Shift:=xlAbove
i = i + 1
Last_Row = Last_Row + 1

Debug.Print i & "-" & Last_Row
End If

Next i  'exits at 1334?

End With

End Sub``````

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### jasonb75

##### Well-known Member
The loop will be running to the initial value of the Last_Row variable, not the new value after you have been adding to it with Last_Row = Last_Row + 1 in the body of the loop.

This is not something that I've personally had to do, but I would suggest trying Do - Loop instead of For - Next, something like this (untested).

VBA Code:
``````Sub Test()

With Sheet_OG

Dim i As Long
Dim Last_Row As Long
Last_Row = .Cells(Rows.Count, 1).End(xlUp).Row
i = 2

Do

If Cells(i, 27) = "Y" Then
Rows(i).Insert Shift:=xlAbove
i = i + 1
Last_Row = Last_Row + 1

Debug.Print i & "-" & Last_Row
End If

Loop Until i = Last_Row

End With

End Sub``````

#### Fluff

##### MrExcel MVP, Moderator
When inserting or deleting rows, you should loop bottom up like
VBA Code:
``````Sub Test()

With Sheet_OG

Dim i As Long
Dim Last_Row As Long
Last_Row = .Cells(Rows.Count, 1).End(xlUp).Row

For i =  Last_Row to 2 Step -1

If Cells(i, 27) = "Y" Then
Rows(i).Insert Shift:=xlAbove

Debug.Print i & "-" & Last_Row
End If

Next i  'exits at 1334?

End With

End Sub``````

#### 2020914

##### New Member
When inserting or deleting rows, you should loop bottom up like
VBA Code:
``````Sub Test()

With Sheet_OG

Dim i As Long
Dim Last_Row As Long
Last_Row = .Cells(Rows.Count, 1).End(xlUp).Row

For i =  Last_Row to 2 Step -1

If Cells(i, 27) = "Y" Then
Rows(i).Insert Shift:=xlAbove

Debug.Print i & "-" & Last_Row
End If

Next i  'exits at 1334?

End With

End Sub``````

This worked perfectly! I haven't done this kind of thing before, so this is a good tip I'll remember for the future. I didn't even think to execute the for loop in reverse. Thank you!

#### Fluff

##### MrExcel MVP, Moderator
Glad we could help & thanks for the feedback.

Replies
33
Views
629
Replies
18
Views
318
Replies
8
Views
65
Replies
2
Views
57
Replies
8
Views
354

1,130,156
Messages
5,640,453
Members
417,143
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back