Moving rows to the bottom of an active table when marked "Completed" in the status column

[kaylairene7]

I am working with a table and trying to have the "completed" tasks move to the bottom of the table automatically when the status is changed on the task to "Completed" I have seen a few threads for this but for some reason when I enter the VBA on the sheet it isn't working.

Any help is super appreciated!!

Thank you!

 

[Joe4]

Welcome to the Board!

Please post us some sample data and your VBA code.

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.

See this thread for how to post your VBA code: How to Post Your VBA Code

 

[kaylairene7]

I am working with A2:F44 for the table. I have done conditional formatting on the status column to turn the cell green when they choose completed from the drop down list. I am hoping that once they choose completed that it will automatically move the completed row to the bottom of the table.

The "Status" column is E.

Please let me know if this is what you need.

 

[Joe4]

Is it really an Excel table?
Or just a bunch of data listed in a tabular form?

The reason I ask because actual Excel tables can be a bit of a pain to work with for stuff like this, and behave a little differently that just data listed in a tabular-like form.

 

[Joe4]

Assuming it really is a table, right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this VBA code in the VB Editor window that pops us:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim r As Long
    
'   Exit if more than one cell updated
    If Target.CountLarge > 1 Then Exit Sub
    
'   Exit if updated cell not in E2:E44
    If Intersect(Range("E2:E44"), Target) Is Nothing Then Exit Sub
    
'   See if value set to "Completed"
    If Target.Value = "Completed" Then
        Application.EnableEvents = False
'       Get updated row
        r = Target.Row
'       Set table name
        Set ws = ActiveSheet
        Set tbl = ws.ListObjects("Table1")
'       Add new row
        tbl.ListRows.Add
'       Copy data down
        Range(Cells(r, "A"), Cells(r, "F")).Cut
        Range("A45").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
'       Delete blank rows
        Rows(r).Delete
        Application.EnableEvents = True
    End If
    
End Sub

*Note that you may need to change the table name in the code if your table name is not "Table1".

Now, as you update the value in E2:E44 to "Completed", it will push them to the bottom of your table.