For Loop exits too soon?

[2020914]

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!

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

 

[jasonb75]

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

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]

When inserting or deleting rows, you should loop bottom up like

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]

When inserting or deleting rows, you should loop bottom up like

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]

Glad we could help & thanks for the feedback.