For Loop exits too soon?

2020914

New Member
Joined
Feb 27, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
12,672
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
56,869
Office Version
  1. 365
Platform
  1. Windows
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
 
Solution

2020914

New Member
Joined
Feb 27, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
56,869
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

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

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?

Disable AdBlock

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
Top