Getting Rid of All Rows Except the One for the Latest Date

@Jevgenijs (see 2019-10-31) wanted to keep track of when a row was last edited to enable identification of rows with the oldest changes. (A row is edited when any cell in that row is edited.) Several solutions have been provided in the comments below. All have a problem: No cell edit(s) can be Undone (Ctrl+Z) because Excel clears the Undo stack when a Sub procedure changes a cell's value. The previous solutions immediately store the date/time of each row's edit in a certain column of that row.
Here is a solution that postpones storing the date/time of each row's edit until the workbook is saved. This allows any cell edit(s) to be Undone prior to saving the workbook. For an applicable worksheet, EditDateColumn identifies the column for storing the date/time of each row's edit; pick a column (A=1, B=2, etc.) that does not have any other data.
1. Press Alt+F11 to open the Visual Basic Editor (VBE).
2. Under VBAProject > Microsoft Excel Objects, click the applicable sheet and press F7 to open the Code pane, then paste the following VBA code (which applies only to the selected worksheet):

Private Sub Worksheet_Change(ByVal Target As Range)
Const EditDateColumn As Long = 1
Call EditDate_Collect(Me, Target, Columns(EditDateColumn))
End Sub

3. Under VBAProject > Microsoft Excel Objects, click ThisWorkbook and press F7 to open the Code pane, then paste the following VBA code (which applies to all worksheets that have the code above):

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Call EditDate_Record(SaveAsUI, Cancel)
End Sub

4. Right-click VBAProject and pick Insert > Module, then paste the following VBA code into the new module:

Dim EditDates As New Collection

Public Sub EditDate_Collect(WS As Worksheet, Target As Range, EDColumn As Range)
Dim EditDate As Collection, LastRow As Long, A As Range
Dim msg As String, s As String, t As String
Const myName As String = "EditDate_Collect"
If EditDates.Count > 0 Then
LastRow = WS.UsedRange.Row - 1 + WS.UsedRange.Rows.Count
For Each A In Target.Areas
If (A.Row <= LastRow) And (A.Address = A.EntireRow.Address) Then
s = EDColumn.Address(False, False)
s = Left(s, (InStr(1, s, ":") - 1))
t = Target.Address(False, False)
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
msg = "To properly maintain edit date/time in column " & s & " of worksheet '" _
& WS.Name & "' it is necessary to save the workbook before" _
& " inserting, deleting, copying, or moving rows on that worksheet." _
& vbNewLine & vbNewLine & "Your latest change to range " & t _
& " has been nullified (undone)." _
& " Please save the workbook before repeating that change."
MsgBox msg, vbCritical, myName
Exit Sub
End If
Next A
End If
For Each A In Target.Areascolumns
If (Intersect(A, EDColumn) Is Nothing) And (A.Address <> A.EntireColumn.Address) Then
Set EditDate = New Collection
EditDate.Add Now, "Date"
EditDate.Add Intersect(A.EntireRow, EDColumn).Address, "Address"
EditDate.Add WS, "WS"
EditDates.Add EditDate
End If
Next A
End Sub

Public Sub EditDate_Record(SaveAsUI As Boolean, Cancel As Boolean)
Dim EditDate As Collection
Application.EnableEvents = False
For Each EditDate In EditDates
EditDate("WS").Range(EditDate("Address")) = EditDate("Date")
Next
Set EditDates = New Collection
Application.EnableEvents = True
End Sub

5. Be careful when inserting, deleting, copying, moving, or sorting columns on the worksheet. Save the workbook as XLSM or XLSB. For a formatted and commented version of this VBA code, see the Google Drive text file EditDate.txt at
https://drive.google.com/open?id=16CestQHL2GusGg-M5CkJHk5LVcVXQHiu

My last procedure works correctly only when CurrentDateColumn C is 1 (column A). Here is an improved version without that limitation.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim A As Range, R As Range
Const C = 1 'CurrentDateColumn
Set R = Columns(C)
For Each A In Target.Areas
If (Intersect(A, R) Is Nothing) And (A.Rows.Count < 65536) Then
Range(Cells(A.Row, C), Cells((A.Row + A.Rows.Count - 1), C)) = Now
End If
Next A
End Sub

As before, there are still two problems with this procedure:
1. It ignores Areas that include CurrentDateColumn; this is probably acceptable.
2. No cell change(s) can be undone (Ctrl+Z); this is left as an exercise for the reader. For example, see
https://wellsr.com/vba/2019/excel/how-to-undo-a-macro-with-vba-onundo-and-onrepeat/

@Willy Vanhaelen
Here is an improved version of your macro. It handles discontinuous ranges and ignores changes involving more than 65535 rows (such as entire columns).

Private Sub Worksheet_Change(ByVal Target As Range)
Dim A As Range
Const C = 1 'CurrentDateColumn
For Each A In Target.Areas
If A.Column <> C And A.Rows.Count < 65536 Then
Range(Cells(A.Row, C), Cells((A.Row + A.Rows.Count - 1), C)) = Now
End If
Next A
End Sub

There are two problems with this macro:
1. It ignores Areas that include CurrentDateColumn; this is probably acceptable.
2. No change(s) can be undone (Ctrl+Z); this is left as an exercise for the reader.

@J. Woolley
As I mentioned, my macro handles only adjacent cells (in a column) but it does no harm if you try discontinuous ranges. Only the first area of the selection will be handled and the others are ignored.

I did no effort to try to make the macro handle discontinuous ranges because in practice this will almost never be necessary.

@Willy Vanhaelen
What if Target is a discontinuous Range (like B1:B10,B21:B30)?

@J. Woolley
Your macro has a flaw. If you delete some row(s) in the sheet, the macro takes a very long time to complete because of the For Each ... Next loop. It runs for every cell in each row (16384) and finaly ends by overwriting the date in the following row(s).

Here is a macro that avoids this because it has no For Each loop and still handles entries in several adjacent cells with Ctrl Enter:

Private Sub Worksheet_Change(ByVal Target As Range)
Const C = 1 'Current date column
If Target.Column = C Then Exit Sub
Dim R As Integer: R = Target.Row
Range(Cells(R, C), Cells(R + Target.Rows.Count - 1, C)) = Now
End Sub

@Jevgenijs

The following VBA code will add the current date/time to a column whenever cells in a row of the worksheet are changed. The current date/time is put in CurrentDateColumn of the applicable row. CurrentDateColumn is set to 1 (column A), but you can specify any column you prefer. After sorting on that column, you can decide which rows to delete or move.

1. Press Alt+F11 to open the Visual Basic Editor (VBE).
2. Under VBAProject > Microsoft Excel Objects, click the applicable sheet and press F7 to open the Code pane, then paste the following VBA code (which applies only to the selected worksheet):

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Const CurrentDateColumn = 1
For Each c In Target.Cells
If c.Column <> CurrentDateColumn Then
Cells(c.Row, CurrentDateColumn).Value = Now
End If
Next c
End Sub

Hi Allen, i was wondering if you could help me. I m looking on a small sample of database, like 5000 entries on excel. Would it be possible to write a function for excel which would work like that IF row has not been edited in the last 19 month (or any time frame i need) then delete entry or move it to another page.

Many thanks

Hi Allen, i was wondering if you could help me. I m looking on a small sample of database, like 5000 entries on excel. Would it be possible to write a function for excel which would work like that IF row has not been edited in the last 19 month (or any time frame i need) then delete entry or move it to another page.

Many thanks