Automatically insert integer in cell when creating new row

Question

I have tried many methods to get this right but all of them (SEQUENCE, ROW(), Fill etc.) do not maintain history-I suspect I may need a little VBA to get this right. How do I check all the integers in a column and produce the next sequence when inserting a new row such that when the sheet is sorted the numbered sequences remain static? For example see below:

I have a table that contains an EMP_ID field, when I insert a new row it should check what values exist and insert the next sequential value (51) and remain static.

Answer 1

It is easy to get the highest number of a column, just use the WorksheetFunction.Max function.

Now all you have to do is to put this logic into the Change-Event of the Worksheet. Check if the entered data is part of the table (tables are called ListObject in VBA) and if the ID column of that row is empty. If yes, put the max ID + 1 into the ID column.

When you add data using Cut&Paste (so multiple values are entered at once), the change event is triggered only once, so I added a loop to process all modified cells.

Put the following code into the Worksheet module of the sheet that contains the table. I assume that the sheet contains only one table and that the ID-column is column 7 (your image is hard to read), maybe you have to adapt the code to your needs.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Const IDCol = 7

    Dim cell As Range
    For Each cell In Target
        If Not Intersect(Me.ListObjects(1).Range, cell) Is Nothing Then    ' Cell is part of the table
            If Not IsEmpty(cell.Value) And cell.Column <> IDCol And IsEmpty(Me.Cells(cell.row, IDCol)) Then
                Dim maxID As Long
                maxID = WorksheetFunction.Max(Me.Columns(IDCol).EntireColumn)
                Me.Cells(cell.row, IDCol) = maxID + 1
            End If
        End If
    Next
End Sub