Auto serial number

[NDMDRB]

Hello,

Please I need your help, if this is any possible way in Excel

I have a userform to show data on a list box, add new data to the sheet, and remove any selected row from the list box and the sheet
I have all these buttons and codes working well, and I have uploaded my test file below

At the moment, if (for example) I removed "Row7", the sort code will run and "Row8" data will be in "Row7" and will have the same serial number "4"

What I'm looking for is:

• ex: If I removed "Row 7" that has a serial number "3", then...
• Run the sort code, then...
• "Row8" data will be in "Row7" but with serial number "3"

In conclusion, I need column "B" to always has a serial number

here is my test file
https://drive.google.com/file/d/1QsoxAwmqRgCkXJkpYtTOebbDacwRVp3W/view?usp=sharing

 

[Bering]

Hello,

Try:

https://drive.google.com/open?id=1S6kzFo6fGFVllaywiCkEDFLOCpKST5K0

 

[Fluff]

@Bering
Can you please post your suggestion to the board, so that everyone can see it without having to download a workbook.

 

[NoSparks]

One way would be to use a formula for the Num's in column "B"
=Row() - 4
and when adding write the formula instead of an actual number to column B

 

[Bering]

@Bering
Can you please post your suggestion to the board, so that everyone can see it without having to download a workbook.

Sorry Fluff:

Module1

original code

Sub SortList()With Sheet1
.Range("B4:G9999").Sort key1:=.Range("B4"), order1:=xlAscending, Header:=xlNo
End With
End Sub

my attempt

Sub SortList()Const IncrementalFactor As Integer = 1
Const FirstRow As Integer = 6


With Sheet1
.Range("B4:G9999").Sort key1:=.Range("B4"), order1:=xlAscending, Header:=xlNo


If .Cells(5, 3) <> "" Then


.Cells(5, 2) = IncrementalFactor


Else


.Cells(5, 2) = ""


End If


    For i = FirstRow To .Cells(.Rows.Count, 3).End(xlUp).Row


        .Cells(i, 2) = .Cells(i - 1, 2) + 1
    
    Next i
    
End With
End Sub

I have also added a On Error Resume Next in the UserForm1 macro to prevent any error if all rows get deleted:

Private Sub cmdRemove_Click()
On Error Resume Next

The issue is in the row below:

ListBox1.RowSource = Sheet1.Range("Items").Address(external:=True)