Generate Auto Number Using VBA

  • Hello All

    I have in my sheet the below formula:
    To generate an automatic sequence number, depend on the cell in H column if it is not blank
    I have a problem in above formula; when delete one row the sequence number following that deleted row gives errors


    I think to change it to VBA
    May I have VBA code doing the same job? But without any problem when deleting any row and the autonumber should remain constant once it has been assigned.

    I hope it is clear

  • Re: Generate Auto Number Using VBA

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim cell As Range
        If Not Intersect(Range("H:H"), Target) Is Nothing Then
            For Each cell In Intersect(Range("H:H"), Target).Cells
                If cell.Value <> "" And Range("A" & cell.Row).Value = "" Then
                    Range("A" & cell.Row).Value = Application.Max(Range("A:A")) + 1
                End If
        End If
    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!