Select, Cut Entire Row and Insert one Row down based on condition

[HansrajR]

I am struggling with selecting and cutting a row based on a condition (Start date "B" < Not Before date "C") for example highlighted cell B2 and inserting previously cut row one row down.

Can someone kindly help me out.

Initial order: A, B, C, D, E, F, G, H and I

Order after processing: B, A, C, D, E, F, G, H and I



The code I am using

Sub cutentirerowInsert1rowdown()

Dim rw As Long

With ActiveWorkbook.ActiveSheet
    For rw = 2 To 10

    .Cells(rw, 2).Select
    
        If .Cells(rw, 2) < .Cells(rw, 3) Then 'If Start date less than Not Before date
            ActiveCell.EntireRow.Select 'Select entire row
            Selection.Cut 'Cut entire row
            ActiveCell.EntireRow.Offset(2, 0).Activate
            Selection.Insert Shift:=xlDown
        End If
    
Next rw
End With

End Sub

 

[vletm]

HansrajR
How A can be before I ?
Isn't it only which condition is False?

 

[HansrajR]

The condition is starting from Row Number 2,

If in Row Number 2, Start date is less than Not Before, the Row Number 2 should be moved one row down, else remain as is.

And then the next row is verified with same condition and outcome.

I realised that the Row Number changes in the process and the Row Number 2 previously moved down is re-processed by the code.

In the example, Name "A" (Row Number 2) at the start is moved completely down when the code is fully executed.

The solution would be to take the Name as identifier.

How to resolve this issue

 

[Marc L]

The solution would be to take the Name as identifier.

No sense …​

​

Anyway according to the attachment a VBA demonstration as a beginner starter :​

Sub Demo1()
        Dim V, W, R&, F%
    With [A1].CurrentRegion.Columns
            V = .Item("B:C").Value2
            W = Evaluate("ROW(1:" & .Rows.Count & ")")
        For R = .Rows.Count - 1 To 2 Step -1
            If V(R, 1) < V(R, 2) Then W(R, 1) = R + 1: W(R + 1, 1) = R: F = 1
        Next
        If F Then
            Application.ScreenUpdating = False
           .Item(.Count + 1).Value2 = W
           .Resize(, .Count + 1).Sort .Item(.Count + 1), 1, Header:=1
           .Item(.Count + 1).Clear
            Application.ScreenUpdating = True
        End If
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[vletm]

HansrajR
You seems to refer to Your code
- which (as You wrote) won't give expected result
- I didn't.
You should able to write clear details/rules
- what should be Your expected result ... based Your four columns?

 

[HansrajR]

Thanks for your help Marc L.

Could you kindly indicate how to change the code to move the row up instead of down.

 

[Marc L]

No sense according to your attachment …​

​

 

[HansrajR]

I have modified the attached file to reflect the situation.



If Start date is greater than Not Before date (as in Row Number 8), the entire row must be cut and inserted only one row up (before F).

However, this condition and action must not be applied to top 3 rows (the header, Row Number 2 and Row Number 3).

That is, the header, Row Number 2 and Row number 3 is fixed even when Start date is greater than Not After date.

For remaining rows, on event the condition Start date is greater than Not Before date is satisfied, the row must not be moved up the header, Row Number 2 and Row Number 3.

For example, in Row Number 4, Start date is greater than Not Before date but cannot be moved up.

 

[HansrajR]

Could you kindly add comments to the code, step by step of what it does and how it works.

Thanks for your help.

Sub Demo1()
        Dim V, W, R&, F%
    With [A1].CurrentRegion.Columns
            V = .Item("B:C").Value2
            W = Evaluate("ROW(1:" & .Rows.Count & ")")
        For R = .Rows.Count - 1 To 2 Step -1
            If V(R, 1) < V(R, 2) Then W(R, 1) = R + 1: W(R + 1, 1) = R: F = 1
        Next
        If F Then
            Application.ScreenUpdating = False
           .Item(.Count + 1).Value2 = W
           .Resize(, .Count + 1).Sort .Item(.Count + 1), 1, Header:=1
           .Item(.Count + 1).Clear
            Application.ScreenUpdating = True
        End If
    End With
End Sub

 

[Marc L]

If necessary it allocates a sort index to each row in a helper column according to post #3 condition …​

​

A good start to learn : as all is yet in VBA help - hit F1 key on any statement - and you can follow the process​

in debug mode via a break point or in step-by-step mode hitting F8 key in order to inspect the variables content​

in the Locals VBE window and to see what happens in the worksheet …​

​

 

[HansrajR]

I have modified the attachment for moving rows up by one row based on condition in my previous reply. Could you kindly have a look.

 

[Marc L]

As this thread was created to move down some rows according to a single criteria​

and solved with post #4 as a starter so you can amend it for anything you have forgotten to well elaborate in the initial post​

so it can't be to move rows up according to several criterions !​

So this thread is confusing, it's better you create a new one but before just well think about your need,​

well elaborate it with details and with a representative attachment and stop to change the rules at each post …​

​