Move an entire row to the bottom of active sheet based on cell value

[jeansarra]

I have list of leads/current clients and there's a column that determines the status of that lead. How do I move the row of a lead where the status says Sold. Help please...

 

[My Aswer Is This]

Re: How to move an entire row to the bottom of active sheet based on cell value

You said:

and there's a column​

We need to know what column.

And do you want this to happen automatically or when you press a button.
And does the cell value change manually or as a result of a formula.

​

 

[JLGWhiz]

Re: How to move an entire row to the bottom of active sheet based on cell value

First of all, it is more efficient when posting a problem to specify the pertinent sheets, cells, columns and/or rows that apply to the problem. That will allow reviewers to determine their best solutions up front.
In this case, if you want to have the row moved automatically upon entry7 of "Sold" in a certain column, then this code assumes that column is column D. If not you will need to change the code where the font is Red.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
    If Not Intersect(Target, Range("[COLOR=#FF0000]D:D[/COLOR]")) Is Nothing And LCase(Target) = "sold" Then
        Target.EntireRow.Cut Cells(Rows.Count, 1).End(xlUp)(2)
    End If
Application.EnableEvents = True
End Sub

This will leave a blank row where the 'Sold' entry is made.
To use the code, save your workbook as a macroenabled workbook, right click on the sheet name tab for the sheet where you want the row to be moved. In the pop up menu, click View Code to open the sheet code module and copy the code into the large pane. Close the vb editor and you are ready to use the code. When you enter sold into a single cell in column D, the code will run. The code will not run, if the entry is generated with a formula, now will it run if several cells in column D are pasted in at the same time.

 

[jeansarra]

Re: How to move an entire row to the bottom of active sheet based on cell value

Thank you so much for the quick help! I am very new to forums and not an excel pro as well so I really appreciate all of your efforts. The column is column K (thanks for the instruction on where I can change that data in the code).

The data is changed from different status chosen from a dropdown by data validation (if it matters). The file also changes and data is added on a daily basis (so i think it would be better if I press a button when I want to make it happen). The file is a lead/client tracker where we put all of the leads we are working on and change the statuses for each as the move forward to selling/buying a home (I work for a real estate company).

Also I'm using Microsoft Excel for Mac version 15.31 on Office 365, does it matter on how the code is added, I tried

This will leave a blank row where the 'Sold' entry is made.

How do I do it without leaving a blank space.

 

[JLGWhiz]

Re: How to move an entire row to the bottom of active sheet based on cell value

This will move your row and delete the blank row that is left.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Dim rw As Long
Application.EnableEvents = False
    If Not Intersect(Target, Range("K:K")) Is Nothing And LCase(Target) = "sold" Then
    rw = Target.Row
        Target.EntireRow.Cut Cells(Rows.Count, 1).End(xlUp)(2)
    Rows(rw).Delete
    End If
Application.EnableEvents = True
End Sub

Delete the original code and replace it with this one.