Auto Update DateTime Field and then sort by it

[2013-10-22]

Using Windows 7, Excel that comes with Office 2010.

Hi,

I have a spreadsheet with various columns included in it.

Column H (Last Update) contains a column with format of dd/mm/yyyy hh:mm

The spreadsheet is used as a work issues list. When I update an item on the spreadsheet, I have been manually updating Col H with the current Date/Time and then re-sorting by Column H to show the most recently updated line at the top of the spreadsheet.

I wondered if it would be possible to:

1. Automatically update the time/date ColH value in the currently selected row when the file is saved
2. Automatically sort by ColH after step 1, so the most recently updated record is at the top of the file.

I realise the downside is that if I update 2 rows it won't be able to cope with that, but if the above could work it would be a big improvement over the current process.

Any advice much appreciated.

Thanks

 

[BrianMH]

You could use the worksheet change event to achieve this. Does the date need updating any time you update anything in the row or just a particular column?

Can you provide some sanitized data in the format you need it?

 

[2013-10-22]

Hi Brian,

Thanks for your reply.

It would useful if colH updated if any of the other cells in the row were changed, but if that is difficult, then the main col to check is colG (Status).

Here is some sample data:

Ref   Date        CR     APP  ENV    Title                      Status                  Last Update        With
================================================================================================================
05    03/01/2014  CR111  GL   ENV1   Finance Report             Tested on ENV1          10/01/2014 10:40   TEAM1
12    07/01/2014  CR123  PA   ENV2   Patch for broken Invoice   With users for testing  10/01/2014 09:50   TEAM1
15    08/01/2014  CR999  AP   ENV3   Patch for faulty Customer  With Bob for approval   10/01/2014 09:50   TEAM2
08    03/01/2014  CR124  GL   TEST   Validation Patch           With Business Analyst   10/01/2014 09:38   TEAM4
10    03/01/2014  CR300  PA   TEST1  Report Performance Issue   Tested, doing docs      09/01/2014 14:32   TEAM1

Thanks again

 

[BrianMH]

Try adding this to your worksheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lBottomRow As Long
    Application.EnableEvents = False
    lBottomRow = Range("A" & Me.Rows.Count).End(xlUp).Row


    'don't modify if there is a header, don't modify if there is nothing in column A
    If Target.Row > 1 And Range("A" & Target.Row) <> "" Then
        Range("H" & Target.Row) = Now()


        Me.Sort.SortFields.Clear
        Me.Sort.SortFields.Add Key:=Range("H2:H" & lBottomRow), _
                          SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        With Me.Sort
            .SetRange Range("A1:I" & lBottomRow)
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End If
exiter:
    Application.EnableEvents = True
    Exit Sub
errhandler:
    MsgBox Err.Number & " - " & Err.Description
    GoTo exiter
End Sub

 

[2013-10-22]

Thank you very much for your very helpful reply!

 

[BrianMH]

Glad to help!