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!

 

[CindyLou]

Ok we're getting there! When I change to Closed, I get 3 messages. 1: Code initiated; 2: Code continued and 3: Conditions met - each requiring you click on OK and then it moves to the bottom.

 

[CindyLou]

Also it looks like when I change a second item to Closed and it gets moved, it's deleting the first row altogether.

 

[Joe4]

Ok we're getting there! When I change to Closed, I get 3 messages. 1: Code initiated; 2: Code continued and 3: Conditions met - each requiring you click on OK and then it moves to the bottom.

If it is working now, then you can remove those 3 Message Box lines, that were just added for debugging.

What did you change that started it working?
Or did you just run the manual macro I posted in my last post?

If the latter, let me explain what is happening. Automated code is called "event procedure" code. It is code that automatically runs upon some "event" happening, like changing a cell, selecting a cell, saving your file, etc. You have to be careful with this kind of code, or else you could get caught in an endless loop. For example, let's say that whenever a value is entered into cell A1, we want to add 5 to it. However, adding 5 to it is entering a new value, so it spurs the code to run again, etc, etc.

So to avoid that from happening, you will often see the following line in the VBA code:
Application.EnableEvents = False
That temporarily "shuts off" the automated event procedure code, so any changes under that line will not trigger the code to call itself and get caught in a loop.
However, that line shuts off ALL event procedure code. So, in order to turn it back on again, we then have another line after our changes like this:
Application.EnableEvents = True

What happens sometimes, is maybe your code gets interrupted (due to an error or other error debugging techniques, like stepping into and out of code), so that the line that shuts it off runs, but it never gets to the line that turns it back on. When that happens, none of the your event procedure code will run automatically (what you were reporting). So running that little manual script I gave is simply turning it back on.

 

[Joe4]

Also it looks like when I change a second item to Closed and it gets moved, it's deleting the first row altogether.

Correct. Isn't that what you wanted?
If not, and you want to leave the "empty" row, then simply delete this line from the code:

Rows(r).Delete

 

[CindyLou]

I want it to move the "closed" row to the bottom and leave it there. It's moving it to the bottom and then once I "close" another row, it's deleting the previous row that was moved.

 

[Joe4]

I want it to move the "closed" row to the bottom and leave it there. It's moving it to the bottom and then once I "close" another row, it's deleting the previous row that was moved.

I don't experience that behavior. Each time I enter "Closed" in column D, I have it moving that row to the bottom, and deleting the blank row.
So the total number of rows never changes, and there are never any "blank" rows left behind by moving a row.

If you do it multiple times, it keeps moving that last row you "Closed" to the bottom.

 

[CindyLou]

Hmm that's weird. When I do it multiple times, it deletes the previous row that moved to the bottom. also - thanks by the way - I super appreciate your help.

 

[Joe4]

When I do it multiple times, it deletes the previous row that moved to the bottom. also - thanks by the way - I super appreciate your help.

So, the total number of rows actually decreases then?
Can you post the version of the code you are using, exactly as you currently have it?

 

[CindyLou]

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

 

[Joe4]

Is column A populated for every single row with data?
If not, can you tell me a column that ALWAYS has an entry in it for every row of data?