Excel VBA: Loop to Fill in Sequence Numbers in a Column

Question

I am new to Excel VBA and I am currently working with a project that needs to autofill sequence numbers in Column A depending on the filled range in Column B.

With the code below that I found in ozgrid.com forum, it autofills column A from 1 up to the last used row in the adjacent cell which is in Column B. But on my case is that I have a userform and it has a command button that when clicked, it should just continue to generate unique Reference ID in Column A from the maximum number on the last filled cell in the Column A up to the last filled cell in the adjacent cell which is the Column B.

And the problem with the code below is that on the first time the command button is clicked, it generates sequence numbers from 1 up to the last used row in Column B, but when the command button is clicked again, Column A is autofilled from 1 to last used row again instead of continuing the max number that is existing in the Column A.

For example, the max number on the first click in the command button is 82.  On the next click of the command button, the cell below the last used row should be 83 up to the last used row in the Column B again.

Also, I have provided a picture of my GUI below for your better reference.

Sub SequenceLoop()
    Dim k, i As Long, n As Long
    Dim LR1 As Long
    Dim ws As Worksheet

Set ws = ThisWorkbook.Sheets("Sheet1")

LR1 = ws.Range("B" & ws.Rows.Count).End(xlUp).Row


With Range("A2:A" & LR1)
    k = .Value
    For i = 1 To UBound(k, 1)
        If Len(k(i, 1)) = 0 Then
            n = n + 1
            k(i, 1) = n
        End If
    Next
    .Value = k
End With 
End Sub

GUI for autofilling data in Column B. When Create Template Button is Clicked, it autofills the specific data in Column B:

The GUI for autofilling sequence numbers in Column A:

       

Answer 1

The solution seems a little clunky, but without knowing everything about what you are trying to do I will not suggest extensive changes.

The solution is pretty simple, you just need to initialize n to the last value in the list before the for loop. The way it is written now, n gets reset to zero every time you run the macro.

Something like the following would do the trick.

n = Cells(Rows.Count, 1).End(xlUp).Value