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]

Welcome to the Board!

Do you mean to the bottom of the data on the same sheet?
Or to another sheet?

Can you show us a small sampling of your data so we can see what it looks like?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Jd2023]

Thank you Joe, I will not be able to share the Excel Spreadsheet due to data protection however please see the screen shot below which I hope will help?

 

[Joe4]

Can you please answer the other questions I asked?

 

[Jd2023]

This would be to the bottom of the data on the same sheet.
Thank you.

 

[Joe4]

OK, a few more questions.

- How many columns of data are there (where does data start/end)?

- For every row of data, is there some column that is required (must always have data in it)?
We would like to identify some column we can look at to determine exactly where the last row of data is.

 

[Jd2023]

There are 30 columns (A- AD), if column AD has data (a date) in it then the entire row needs to drop to the bottom of the list.

No some columns may be empty, however column D always needs to be filled.

 

[Joe4]

There are 30 columns (A- AD), if column AD has data (a date) in it then the entire row needs to drop to the bottom of the list.

No some columns may be empty, however column D always needs to be filled.

Perfect. Thank you.

Right-click on the sheet tab name at the bottom of your screen, select "View Code", and paste the following VBA code in the VB Editor window that pops up.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    Dim lr As Long
    Dim r As Long
    
'   See if any cell in column AD updated
    Set rng = Intersect(Target, Range("AD:AD"))
    
'   Exit if no cell in column AD updated
    If rng Is Nothing Then Exit Sub
    
'   Loop through updated cells
    Application.EnableEvents = False
    For Each cell In rng
'       See if column AD updated with a valid date
        If IsDate(cell) Then
'           Find last row with data in column D
            lr = Cells(Rows.Count, "D").End(xlUp).Row
'           Move row to bottom of sheet
            r = cell.Row
            Rows(r).Cut
            Cells(lr + 1, "A").Select
            ActiveSheet.Paste
            Application.CutCopyMode = False
'           Delete old row
            Rows(r).Delete
        End If
    Next cell
    Application.EnableEvents = True
    
End Sub

This should automatically do what you want. Test it out, and see how it works!

 

[Jd2023]

Thanks Joe, it does not seem to work quite right. The data is going to the top and replaces the first row of data

 

[Jd2023]

Ignore me Joe it worked perfect, thank you! I needed to ensure column D was filled in. You are amazing!