Okay… This one has always bugged me.  I like to have an ID or identity field on my rows even in spreadsheets just so that when I resort them someone can still say “Look at item #67” and I know what they are talking about.

Well I pieced together some stuff I found on the web (I think this was where I found the example of the max_Each_Column function) and this appears to do the trick. Very useful and I absolutely love it.  It requires a macro enabled excel file, but that is the price we pay…

Here is a YouTube video of it…

Add this code to your worksheet and then modify it as needed  to get the correct columns.  As-is when you type in column b and then leave the cell it will add a value to the cell in column A.  It looks in the column A, finds the max value and then adds one to it.  If the ID already has a value then it leaves it alone, so editing rows doesn’t repopulate your ID field.  Easy peasy right?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then 'This is the column that causes the ID to be created
'This uses the target offset so it assumes in two spots below that the column to update is "A" and
'that the column is one position to the left of the target column above
    If Target.Offset(0, -1).Value = "" Then
       Target.Offset(0, -1).Value = Application.WorksheetFunction.Max(Columns("A")) + 1
    End If
End If

End Sub

Function Max_Each_Column(Data_Range As Range) As Integer
    Dim TempArray() As Double, i As Long
        If Data_Range Is Nothing Then Exit Function
        With Data_Range
        ReDim TempArray(1 To .Columns.Count)
        For i = 1 To .Columns.Count
            TempArray(i) = Application.Max(.Columns(i))
        Next
    End With
        Max_Each_Column = TempArray
End Function