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!
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 D, if not exit sub
If Target.Column <> 4 Then Exit Sub
' See if cell just updated set to "Closed"
If Target.Value = "Closed" Then
r = Target.Row
' Move to end
Application.EnableEvents = False
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
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long
MsgBox "1: Code initiated"
If Target.CountLarge > 1 Then Exit Sub
' See if cell just updated is in column D, if not exit sub
If Target.Column <> 4 Then Exit Sub
MsgBox "2: Code continued"
' See if cell just updated set to "Closed"
If Target.Value = "Closed" Then
MsgBox "3: Conditions met"
r = Target.Row
' Move to end
Application.EnableEvents = False
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
Sub ReenableEvents()
Application.EnableEvents = True
MsgBox "Events have been reenabled"
End Sub