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!
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?You are welcome.
Maybe something like this: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.
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
It worked!!! Thank you very much!!!Maybe something like this:
In this example, I am looking for a change in columns B or D, and putting the timestamp in column J.VBA Code: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
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.
Do you mean to delete the blank row instead of leaving it there?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?
Rows(r).Delete