Moving row to bottom of sheet when status changed

[watsont84]

I would like to have a row moved to the bottom of the sheet once the status is changed to Call Completed in the dropdown menu. I'm not sure what code to use. The status column is E2:E40.

 

[Joe4]

Welcome to the Board!

Without any details, it is very difficult to offer personalized assistance. It would be most helpful if you could show us a sample of your data so we can see all the columns you are using and how the data is structured.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Joe4]

Here is code that will work, as long as we can use column E to find the last row of data (meaning column E is ALWAYS populated for any row with data).
If that is not the case, it will need tweaking depending on what your data looks like.

First click on the sheet tab name at the bottom of your screen, select "View Code", and paste this VBA code in the VB Editor window that pops up:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim r As Long

'   Exit if multiple cells being updated at once
    If Target.CountLarge > 1 Then Exit Sub
    
'   See if update made in E2:E40 (if not, exit)
    If Intersect(Target, Range("E2:E40")) Is Nothing Then Exit Sub
    
'   See if updated to "Call Completed"
    If Target.Value = "Call Completed" Then
'       Find next available row at bottom to move to
        r = Cells(Rows.Count, "E").End(xlUp).Row + 1
'       Move row to bottom
        Application.EnableEvents = False
        Rows(Target.Row).Copy Cells(r, "A")
'       Delete original row
        Rows(Target.Row).Delete
        Application.EnableEvents = True
    End If
        
End Sub

Then, as you update column E from the drop-down, the code will fire automatically and do what you want.