• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Cut and insert entire row one row down on event a condition is statisfied - Endless Loop

HansrajR

Member
Can someone kindly help me out with this.

Below is a VBA code to cut and insert an entire row one row down on event a condition is statisfied and all rows are re-calculated with a VBA macro.

But it continuous as an endless loop when successive rows below also satisfy the condition, so the code must be improved to carry over these rows also.

The error starts at Row Number 87 in the attached sample data.

Code:
Sub cutentirerowInsert2rowsdown()

Dim rw As Long

With ActiveWorkbook.ActiveSheet
    LastRow = .UsedRange.Rows.Count
    For rw = 4 To LastRow
 
    .Cells(rw, 5).Select
      
        If .Cells(rw, 5) < .Cells(rw, 11) Then 'If Start date less than Not Before date
            Do Until .Cells(rw, 5) >= .Cells(rw, 11)
            .Cells(rw, 5).EntireRow.Select 'Select entire row
            Selection.Cut
            .Cells(rw + 2, 5).EntireRow.Select
            Selection.Insert Shift:=xlDo
            Loop
        
        End If
      
      Copy_formula_from_rowNumber3_paste_up_to_last_row
      
Next rw
End With

End Sub
 

Attachments

  • CutInsertEntireRow.xlsm
    72.7 KB · Views: 5
Just a bad code or some bad criteria but as you forgot to explain what exactly should be done and why​
it's just a guessing challenge so very not the purpose of any Excel forum …​
 
If Start date is less than Not Before date for a row (Starting from Row Number 4), the entire row is cut and inserted one row down.

The code is working correctly as long as in the next row the Start date is greater or equal to Not before date.

But on event, the Start date is less than Not Before date both in the row being inspected and the following row as it is the case as from Row Number 87 and Row Number 88, the rows interchange continuously and there is a continuous loop.

So I included an ElseIf Statement with a Loop count, which sums the number of succeeding rows with Start date less than Not Before date, select all this rows and insert after the row in which Start Date is greater or equal to Not Before date.

But I am having an error in the following line:

Code:
Rows("rw:rw+L").Select Error in this line

How to make select all rows whereby ElseIf condition is not satisfied

The full code is as follows:

Code:
Sub cutentirerowInsert2rowsdown()

Dim rw As Long
Dim L As Integer

With ActiveWorkbook.ActiveSheet
    LastRow = .UsedRange.Rows.Count
    For rw = 4 To LastRow
 
    .Cells(rw, 5).Select
      
        If .Cells(rw, 5) < .Cells(rw, 11) And .Cells(rw + 1, 5) >= .Cells(rw + 1, 11) Then 'If Start date less than Not Before date in the row being inspected and Start date is greater or equal to Not Before date in next row
            Do Until .Cells(rw, 5) >= .Cells(rw, 11)
                .Cells(rw, 5).EntireRow.Select 'Select entire row
                Selection.Cut
                .Cells(rw + 2, 5).EntireRow.Select
                Selection.Insert Shift:=xlDown
            Loop
            
                ElseIf .Cells(rw, 5) < .Cells(rw, 11) And .Cells(rw + 1, 5) < .Cells(rw + 1, 11) Then 'If Start date less than Not Before date in the row being inspected and next row and further succeeding rows
                Do Until .Cells(rw + L, 5) >= .Cells(rw + L, 11)
                    Rows("rw:rw+L").Select 'Select all rows ElseIf condition is satisfied
                    Selection.Cut
                    .Cells(rw + L + 2, 5).EntireRow.Select 'Ínsert all selected rows under the row whereby Start Date is  greater than Not Before Date
                    Selection.Insert Shift:=xlDown
                    
                    L = L + 1
                    
                Loop
        End If
      
      Call Copy_formula_from_rowNumber3_paste_up_to_last_row
      
Next rw
End With

End Sub
 
HansrajR
I'm curious, what do You expect to happen with Your below code?
Code:
Rows("rw:rw+L").Select
Could You mean
Code:
Rows(rw & ":" & rw + L ).Select
... but why do You Select something?
 
The code is working correctly
In fact your logic fails as you must have seen in one of your threads like in many about deleting / inserting rows​
the loop must start from the last row up to the top or the counter loop must be updated accordingly …​
As a good VBA procedure does not need to select anything and for a fast execution it must avoid to cut / insert / move any row​
like I already showed you in your previous thread just applying a sort to the rows leading to an instant result !​
 
Got it right.

Please try with following attachment.

The process is a moving rows procedure based on a condition rather than sorting. This process should start from the top rows to the bottom.

Each time a row is moved, the values of all cells in the spreadsheet is re-calculated and therefore change (except for from Row Number 1 to Row Number 3) and conditional statement is verified. If the conditional statement is satisfied in the Row number, the process moves to the next row number.

Code:
Sub Start_Not_Before()

Dim rw As Long
Dim L As Integer

With ActiveWorkbook.ActiveSheet
    LastRow = .UsedRange.Rows.Count
    For rw = 4 To LastRow
 
    .Cells(rw, 6).Select 'Select Cell in row with Start date "F"
      
        If .Cells(rw, 6) < .Cells(rw, 15) And .Cells(rw + 1, 6) >= .Cells(rw + 1, 15) Then 'If, in selected row, Start "F" < Not Before "O" and in next row Start "E" >= Not Before "O"
            
            Do Until .Cells(rw, 6) >= .Cells(rw, 15) 'Until, in selected row Start "F" >= Not Before "O"
                
                .Cells(rw, 5).EntireRow.Select 'Select row
                Selection.Cut 'Cut row
                .Cells(rw + 2, 6).EntireRow.Select 'Select 2 rows down
                Selection.Insert Shift:=xlDown 'Insert previously cut row
                    
            Loop
            
            Call Calculate_Fields_Start_Cut_Date_and_Harvest_Age
            
                    ElseIf .Cells(rw, 6) < .Cells(rw, 15) And .Cells(rw + 1, 6) < .Cells(rw + 1, 15) Then 'If, in both selected row and next row, Start "F" < Not Before "O"
                    
                        Do Until .Cells(rw + L + 1, 6) >= .Cells(rw + L + 1, 15) 'Until, Start "F" >= Not Before "O" in row after loop
                            
                            Rows(rw & ":" & rw + L).Select 'Select all rows until Start "F" >= Not Before "O" in row after loop
                            Selection.Cut 'Cut all rows in loop
                            .Cells(rw + L + 2, 6).EntireRow.Select 'Ínsert all selected rows under the row whereby Start Date "F" >= Not Before "O" after loop
                            Selection.Insert Shift:=xlDown
                        
                            L = L + 1 'Count Number of Loops
                            
                        Loop
                    
                        Call Calculate_Fields_Start_Cut_Date_and_Harvest_Age
                
        End If
        
        Call Calculate_Fields_Start_Cut_Date_and_Harvest_Age
      
    Next rw 'Next Row

Dim OutPut As Integer

OutPut = MsgBox("Succesessfully Completed the Task.", vbInformation, "Move Rows")

End With

End Sub
 

Attachments

  • Harvest Planning.xlsm
    185.7 KB · Views: 2
HansrajR
Hint: You should start to mark even some of Your open same kind of threads as solved.
 
Last edited:
The issue with the code is in the ElseIf statement on event there is no row further down to satisfy the .Cells(rw + L + 1, 6) >= .Cells(rw + L + 1, 15) conditional statement to end the loop.

If so happens, the rows being processed must be left at their original position.
 
As your code does not match your post #3 'explanation' so according to the initial attachment​
post an attachment of the exact expected result and well elaborate all conditions …​
 
Kindly find code and related attachment.

Do not worry about empty cells, it does not affect the process.

The process starts from Row Number 4.

When Start date < Not Before date, the row is cut and inserted one row down.

Since the code takes into account Row Number, the New Row Number 4 is re-verified. On event, Start date < Not Before date, the row is cut and inserted one row down again.

This process continues in Row Number 4 until Start date >= Not Before date. And then, the process continues to Row Number 5.

An issue arises when Start date < Not Before date in 2 following rows. When this is the case, the 2 rows swap endlessly.

So I included the ElseIf statement, which counts the number of times the loop has been repeated until the next row with Start date >= Not Before date is reached - selects all rows through which loop has been repeated to cut and insert below the row with Start date >= Not Before date.

Code:
Sub Test()

Dim rw As Long
Dim L As Integer

With ActiveWorkbook.ActiveSheet
    LastRow = .UsedRange.Rows.Count
    For rw = 4 To LastRow
 
    .Cells(rw, 15).Select 'Select Cell in row with Start date "O"
      
        If .Cells(rw, 15) < .Cells(rw, 28) And .Cells(rw + 1, 15) >= .Cells(rw + 1, 28) Then 'If, in selected row, Start "O" < Not Before "AB" and in next row Start "O" >= Not Before "AB"
            
            Do Until .Cells(rw, 15) >= .Cells(rw, 28) 'Until, in selected row Start "O" >= Not Before "AB"
                
                .Cells(rw, 15).EntireRow.Select 'Select row
                Selection.Cut 'Cut row
                .Cells(rw + 2, 15).EntireRow.Select 'Select 2 rows down
                Selection.Insert Shift:=xlDown 'Insert previously cut row
                    
            Loop
            
            Call ComputeStartCutDateandHarvestAge 'Re-calculate Start date and Age as from Row Number 4 by copying formula in Row Number 3 to Row Number 4 upto last row
            
                    ElseIf .Cells(rw, 15) < .Cells(rw, 28) And .Cells(rw + 1, 15) < .Cells(rw + 1, 28) Then 'If, in both selected row and next row, Start "O" < Not Before "AB"
                    
                        Do Until .Cells(rw + L + 1, 15) >= .Cells(rw + L + 1, 28) 'Until, Start "O" >= Not Before "AB" in row after loop
                            
                            Rows(rw & ":" & rw + L).Select 'Select all rows until Start "O" >= Not Before "AB" in row after loop
                            Selection.Cut 'Cut all rows in loop
                            .Cells(rw + L + 2, 15).EntireRow.Select 'Ínsert all selected rows under the row whereby Start Date "O" >= Not Before "AB" after loop
                            Selection.Insert Shift:=xlDown
                        
                            L = L + 1 'Count Number of Loops
                            
                        Loop
                    
                        Call ComputeStartCutDateandHarvestAge 'Re-calculate Start date and Age as from Row Number 4 by copying formula in Row Number 3 to Row Number 4 upto last row
    
        End If
      
    Next rw

Dim OutPut As Integer

OutPut = MsgBox("The processing is complete.", vbInformation, "Harvest Sequence Optimizer")

End With

End Sub
 

Attachments

  • Test.xlsm
    216 KB · Views: 4
As you still want to not follow any advice with your slowest 'logic' and without any attachment for the expected result​
so first put in comment the Select codeline between the For and If codelines like both Call codelines​
in order to see what happens during the execution …​
To find out where your logic fails, restart from the before state and just operate manually when a condition matches​
in order to well observe what happens when you cut a row, insert a row, etc …​
And write notes in order to well elaborate your need if you still need help.​
Last reminders :​
• as any good VBA procedure does very not need to Select any cell at 99.9999% of the time or the execution is dramatically slowed down !​
• According to Excel rules when deleting / inserting rows the logic requires to loop from the last row up to the top …​
• For an instant execution - less than a second - better than deleting / inserting row is to use a helper column to sort the rows​
like yet demonstrated in your previous thread about the same subject …​
 
Back
Top