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!

 

[Legacy 143009]

My suggestion will be:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim tempArray As Variant
  With Application
  tempArray = Rows(Target.Row)
  .EnableEvents = False
  .ScreenUpdating = False
  Target.EntireRow.Delete
  Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, UBound(tempArray, 2)).Value = tempArray
  .ScreenUpdating = True
  .EnableEvents = True
  End With
End Sub

 

[RedStag750]

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

Yes, I tried to reply saying never mind because I realized I got rid of the row delete to see what it would do. Now I am having a problem when the row it moves to the bottom is removed from the table it was in, which messes up some of the lookup code I have inserted in for it

 

[Joe4]

Yes, I tried to reply saying never mind because I realized I got rid of the row delete to see what it would do. Now I am having a problem when the row it moves to the bottom is removed from the table it was in, which messes up some of the lookup code I have inserted in for it

The original question was not dealing with tables. Dealing with tables is different than dealing with lists/rows of data that are not contained in tables.
You should probably ask your question in its own, new thread, since it is a very different situation than the original question that was posed in this thread.

 

[viceman57]

Right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this code in the resulting VB Editor window that pops up.

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

This code should run autoamtically when you update a cell in column D to "Closed".

Is there a way to use this code with paste values? I am able to move my rows but it is moving them with formulas in tact. I just want to move the values over

 

[Joe4]

Is there a way to use this code with paste values? I am able to move my rows but it is moving them with formulas in tact. I just want to move the values over

I see that you posted this question to its own thread, which is probably the best thing to do.
But please just be sure to only post the question one time (see rule 12 here for rules on Duplicate Posting: Message Board Rules).
I am locking this thread and leaving your new question open.