Move row automatically to the bottom of the sheet when cell changes

[CindyLou]

I have a column of "Open" and "Closed" items. When the cell is changed to "Closed," I'm trying to get the whole row to go to the bottom of the spreadsheet. I've tried following a few other threads with no luck. Thanks!

 

[CindyLou]

Yes, A is populated in every row.

 

[Joe4]

That does not seem to make any sense to me. You should not be losing any rows.
Can you create a small example where this is happening, and post images of your "before" and "after" data.
You can post images using the tools mentioned here: XL2BB - Excel Range to BBCode

 

[CindyLou]

I think I figured out why it was happening to me. When I created "test" rows, I did have anything in Column A. I just tested it again with a value in A and it worked. Woohoo! I'm so excited. Thank you Joe!!

 

[Joe4]

You are welcome.

 

[JohnEJo]

You are welcome.

I realize this is an old thread, but quick question for you. Is there a way to make this work looking at changes in two or more cells, instead of the one in column "D"? I am creating a spreadsheet that will need to look if there was a change in one of two columns, or more, to determine if the row needs to be moved to the bottom. One side note, I am also looking to have a time stamp automatically entered into a different cell if there is a change in one or both columns. Would you code allow that to automatically populate the time stamp before moving the row, or would there be a break somewhere in the process?

Wait a minute. A thought just crossed my mind and tell me if this would work. If either of the two cells in a row are changed, then a time stamp is automatically added into another cell. Could your code trigger the moving of the row to the bottom of the list based on if a time stamp is automatically entered into a cell in another column?

Thank you.

 

[Joe4]

I realize this is an old thread, but quick question for you. Is there a way to make this work looking at changes in two or more cells, instead of the one in column "D"? I am creating a spreadsheet that will need to look if there was a change in one of two columns, or more, to determine if the row needs to be moved to the bottom. One side note, I am also looking to have a time stamp automatically entered into a different cell if there is a change in one or both columns. Would you code allow that to automatically populate the time stamp before moving the row, or would there be a break somewhere in the process?

Wait a minute. A thought just crossed my mind and tell me if this would work. If either of the two cells in a row are changed, then a time stamp is automatically added into another cell. Could your code trigger the moving of the row to the bottom of the list based on if a time stamp is automatically entered into a cell in another column?

Thank you.

Maybe something like this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim r As Long

    If Target.CountLarge > 1 Then Exit Sub
    
'   See if cell just updated is in column B or column D
    If (Target.Column = 2) Or (Target.Column = 4) Then
        Application.EnableEvents = False
        r = Target.Row
'       Update timestamp in column J
        Cells(r, "J") = Now()
'       Move row to the end
        Rows(r).Cut
        Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select
        ActiveSheet.Paste
        Rows(r).Delete
        Application.EnableEvents = True
    End If

End Sub

In this example, I am looking for a change in columns B or D, and putting the timestamp in column J.
I am also not looking for any particular value (as you did not mention any), just any change at all.
It would be easy enough to add other conditions.

 

[JohnEJo]

Thank you. I'll give it a try later today. Thank you much!!!

 

[JohnEJo]

Maybe something like this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim r As Long

    If Target.CountLarge > 1 Then Exit Sub
   
'   See if cell just updated is in column B or column D
    If (Target.Column = 2) Or (Target.Column = 4) Then
        Application.EnableEvents = False
        r = Target.Row
'       Update timestamp in column J
        Cells(r, "J") = Now()
'       Move row to the end
        Rows(r).Cut
        Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select
        ActiveSheet.Paste
        Rows(r).Delete
        Application.EnableEvents = True
    End If

End Sub

In this example, I am looking for a change in columns B or D, and putting the timestamp in column J.
I am also not looking for any particular value (as you did not mention any), just any change at all.
It would be easy enough to add other conditions.

It worked!!! Thank you very much!!!

 

[RedStag750]

Whenever I make the change to "Closed" It'll move it to the bottom, but it leaves the row it was previously in blank. Is there a way to make it not do that?

 

[Joe4]

Whenever I make the change to "Closed" It'll move it to the bottom, but it leaves the row it was previously in blank. Is there a way to make it not do that?

Do you mean to delete the blank row instead of leaving it there?
You would just need to add a line like:

Rows(r).Delete