Autogenerate Serial Number with Rows Automatically Sorted According to Priority Column

Question

I require a macro to autogenerate a serial number on Column A as soon as information is entered in column B. Column C allows for prioritisation of the information in column B. I currently have a macro that sorts the rows according to prioritisation. Thus priorities 1 would be sorted at top of the sheet, followed by 2 etc. Thus the question how to autogenerate a serial number taking in consideration that the sort macro will move the rows around according to assigned priority.

Serial number should be a simple numeric sequence starting at 1,2,3..... Not sure how query for the last number used in order to create next number.

Answer 1

I imagine it will be some variation of =MAX(A:A)+1

If you are using Excel 2003 you may want to restrict the range to say =MAX(A1:A65536)+1

and in VBA

Option Explicit

Sub test()
Dim lookuprng As Range
Dim nextSerial As Long

Set lookuprng = Sheet1.Range("A:A")
nextSerial = WorksheetFunction.Max(lookuprng) + 1
End Sub

Answer 2

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range, c As Range
    Set rng = Application.Intersect(Target, Me.Columns(2))
    If Not rng Is Nothing Then
        'protect against processing (eg) the insertion of a whole column
        If rng.Cells.Count < 100 Then
            For Each c In rng.Cells
                If Len(c.Value) > 0 And Len(c.Offset(0, -1).Value) = 0 Then
                    c.Offset(0, -1).Value = GetNextNumber()
                End If
            Next c
        End If
    End If

End Sub

Function GetNextNumber()
    Dim rv
    With Me.Range("H1")
        rv = .Value
        .Value = .Value + 1
    End With
    GetNextNumber = rv
End Function