[SOLVED] Autolnumber based on last cell in a row value

Autolnumber based on last cell in a row value

Following code adds auto-numbers in column A if column B has a value, I would like to change that code so it would start to add autonumber counting from the last row down. Why?, because if I delete any of the rows with numbers already in the first column, ie. after deleting 5th and 6th row I have sequence in first column 1, 2, 3, 4, 7, 8. After I run my code it will change it back to 1, 2, 3, 4, 5, 6, but if it could start from last row down it would/should look like this: 1, 2, 3, 4, 7, 8, 9, 10, etc. In other words it should not change the existing rows just continue with numbering newly added rows. How can I do it?

Sub AutoNumber()
    Dim lastRow As Long
    lastRow = Range("B" & Rows.Count).End(xlUp).Row
    Range("A1").AutoFill Destination:=Range("A1:A" & lastRow), Type:=xlFillSeries
End Sub

Solution

I prefer DataSeries over AutoFill for a sequential series.

Sub AutoNumber()
    Dim lastRowA As Long, lastRowB As Long
    lastRowA = Range("A" & Rows.Count).End(xlUp).Row
    lastRowB = Range("B" & Rows.Count).End(xlUp).Row
    Range(cells(lastrowa, "A"), cells(lastrowb, "A")).DataSeries _
       Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, Step:=1, Trend:=False
End Sub