Move row automatically to the bottom of the sheet when cell changes

CindyLou

New Member
Joined
Jun 23, 2020
Messages
12
Office Version
365
Platform
Windows
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.
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

CindyLou

New Member
Joined
Jun 23, 2020
Messages
12
Office Version
365
Platform
Windows
Also it looks like when I change a second item to Closed and it gets moved, it's deleting the first row altogether.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,476
Office Version
365
Platform
Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,476
Office Version
365
Platform
Windows
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:
VBA Code:
Rows(r).Delete
 

CindyLou

New Member
Joined
Jun 23, 2020
Messages
12
Office Version
365
Platform
Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,476
Office Version
365
Platform
Windows
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

New Member
Joined
Jun 23, 2020
Messages
12
Office Version
365
Platform
Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,476
Office Version
365
Platform
Windows
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

New Member
Joined
Jun 23, 2020
Messages
12
Office Version
365
Platform
Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,476
Office Version
365
Platform
Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,100,028
Messages
5,472,089
Members
406,800
Latest member
TeachMeExcelPlease

This Week's Hot Topics

Top