How To Move Entire Row To The Bottom Of Active Sheet Based On Cell Value In Excel

[Jd2023]

Hi all,

I would like to automatically move an entire row to the bottom of the sheet when column 'AD' is populated with a date?

I would appreciate any help with this.

Kind Regards
Jasmine

 

[Joe4]

You are welcome. Here is a little shorter/simpler version:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim lr As Long
    Dim r As Long
    
'   Exit if more than one cell updated at a time
    If Target.CountLarge > 1 Then Exit Sub
    
'   Exit it cell updated not on column AD
    If Target.Column <> 30 Then Exit Sub
    
'   See if date entered in column
    If IsDate(Target) And Target > 0 Then
        Application.EnableEvents = False
'       Find last row with data in column D
        lr = Cells(Rows.Count, "D").End(xlUp).Row
'       Move row to bottom of sheet
        r = Target.Row
        Rows(r).Cut
        Cells(lr + 1, "A").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
'       Delete old row
        Rows(r).Delete
        Application.EnableEvents = True
    End If
    
End Sub

 

[Jd2023]

Thanks Joe, the code is no longer working? Would this be because I made the sheet into a table?

 

[Jd2023]

You are welcome. Here is a little shorter/simpler version:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim lr As Long
    Dim r As Long
   
'   Exit if more than one cell updated at a time
    If Target.CountLarge > 1 Then Exit Sub
   
'   Exit it cell updated not on column AD
    If Target.Column <> 30 Then Exit Sub
   
'   See if date entered in column
    If IsDate(Target) And Target > 0 Then
        Application.EnableEvents = False
'       Find last row with data in column D
        lr = Cells(Rows.Count, "D").End(xlUp).Row
'       Move row to bottom of sheet
        r = Target.Row
        Rows(r).Cut
        Cells(lr + 1, "A").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
'       Delete old row
        Rows(r).Delete
        Application.EnableEvents = True
    End If
   
End Sub

Hi Joe, any chance you could help please! Really need your expertise?

 

[Joe4]

Sorry, I have been out of town all week on a family vacation.

Yeah, if you want to do this, I would probably NOT recommend making it a table, as you would probably need to insert a new row into the bottom the table first before moving the row down to it.
It is a bit more work to do that.
Is there some reason you need to make it a table?

 

[Jd2023]

Ah no worries, hope you had a great vacation!

I converted it back to a range instead of a table and it is working now, thank you!

I applied conditional formatting so the row changes colour too. Can I ask is there a way when the row drops to the bottom it leaves a blank row in its place?

 

[Joe4]

Ah no worries, hope you had a great vacation!

I converted it back to a range instead of a table and it is working now, thank you!

I applied conditional formatting so the row changes colour too. Can I ask is there a way when the row drops to the bottom it leaves a blank row in its place?

I documented the code, trying to make it obvious what each step is doing. So just delete the section that deletes the space where the old row was if you do not to eliminate the residual blank row, i.e. delete these two lines in the code:

'       Delete old row
        Rows(r).Delete

 

[Jd2023]

Great, thank you!

Last question, I would also like to automatically group rows together when a name is populated in column A?

Is that possible? Names would be, Sarah, Jane, Julie

 

[Joe4]

Great, thank you!

Last question, I would also like to automatically group rows together when a name is populated in column A?

Is that possible? Names would be, Sarah, Jane, Julie

That is an entirely new/different question, and as such, should be posted in its own new thread.

 

[Jd2023]

Yes, I did post it but did not get a reply, I will do it again.

 

[Joe4]

Yes, I did post it but did not get a reply, I will do it again.

No! Please don't do that if you have already posted it, as that would be in violation of rule 12 here, regarding duplicate posts: Message Board Rules
Please just bump that post by replying to it again.