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

Status
Not open for further replies.

CindyLou

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

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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"?
 
Upvote 0
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 :)
 
Upvote 0
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".
 
Upvote 0
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"?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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

  • Capturevba.PNG
    Capturevba.PNG
    7.6 KB · Views: 37
Upvote 0
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?
 
Upvote 0
Status
Not open for further replies.

Forum statistics

Threads
1,212,938
Messages
6,110,788
Members
448,297
Latest member
carmadgar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top