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
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!
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,476
Office Version
365
Platform
Windows
Welcome to the Board!

Please provide us with more details, such as:
- What range is your data in (rows/columns)?
- What column are the "Open" and "Closed" entries?
- How are those entries updated (manually, importing, formulas, copy/paste, etc)?
- Do you want this move to be automatic upon entering "Closed"?
 

CindyLou

New Member
Joined
Jun 23, 2020
Messages
12
Office Version
365
Platform
Windows
Hi Joe - thank you! My open and closed entries are in column D, rows 2 through 30. Data so far is A2:E30, but will likely be adding rows. The "Open" will be updated to "Closed" manually. I would be giddy if the row moved when Closed is entered :)
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,476
Office Version
365
Platform
Windows
Right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this code in the resulting VB Editor window that pops up.
VBA Code:
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
This code should run autoamtically when you update a cell in column D to "Closed".
 

CindyLou

New Member
Joined
Jun 23, 2020
Messages
12
Office Version
365
Platform
Windows
Thanks Joe. I could not get it to work for me.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,476
Office Version
365
Platform
Windows
How does it not work for you?
Do you get an error, or just nothing happens?
What is the name of the module you have dropped this VBA code into?
Are you typing in "Closed" exactly like that, or could it also look like "CLOSED" or "closed"?
 

CindyLou

New Member
Joined
Jun 23, 2020
Messages
12
Office Version
365
Platform
Windows
Nothing happens. When I right click on the sheet to View Code, it takes me to "Sheet1", not a module. I tried Module1 also. Either way, nothing happens when I change column D from Open to Closed. I am spelling it Closed.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,476
Office Version
365
Platform
Windows
Yes, it should be in the sheet module (i.e. "Sheet1"). It will not work in any other module.

Try this, and tell me all the Message Boxes that pop-up:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim r As Long
    
    MsgBox "1: Code initiated"

    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
    MsgBox "2: Code continued"
    
'   See if cell just updated set to "Closed"
    If Target.Value = "Closed" Then
        MsgBox "3: Conditions met"
        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
 

CindyLou

New Member
Joined
Jun 23, 2020
Messages
12
Office Version
365
Platform
Windows
I'm new at this so bear with me and thank you for your help and patience. When I open View Code, Sheet1 is under the folder MS Excel Objects. Module1 is under the folder Module. I tried the above code in both places and nothing happened when I changed Open to Closed.
 

Attachments

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,476
Office Version
365
Platform
Windows
Put the code I gave you under "Sheet1". That is where it MUST go in order to run automatically.
It also needs to be named exactly as I sent it. If you change the name of it at all, it will not work. You pretty much have to copy and paste it "as-is".

Do you have VBA enabled? If you Security Settings are disabling VBA, it will not work.

Also, make sure that Events are enabled. Put the following macro in any module (it does not matter), and manually run it.
VBA Code:
Sub ReenableEvents()
    Application.EnableEvents = True
    MsgBox "Events have been reenabled"
End Sub
If you run this successfully, you will get a message box letting you know that events have been reenabled. Once you get that to work, then try updating another field to "Closed".

One last thing, this field you are updating to "Closed" is in column D, right?
 

Watch MrExcel Video

Forum statistics

Threads
1,100,040
Messages
5,472,127
Members
406,805
Latest member
AlesD6

This Week's Hot Topics

Top