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

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

HansrajR

Member
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

74762

The code I am using

Code:
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
 

Attachments

Last edited:

HansrajR

Member
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

Excel Ninja
The solution would be to take the Name as identifier.
No sense …​
Anyway according to the attachment a VBA demonstration as a beginner starter :​
Code:
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

Excel Ninja
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

Member
I have modified the attached file to reflect the situation.

74771

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.
 

Attachments

HansrajR

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

Thanks for your help.

Code:
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

Excel Ninja
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

Member
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

Excel Ninja
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 …​
 
Top