In Column A, user input the date. Column B will have the Serial number. At 1st i type in Formula,
if(isblank(A10),"",B9+1)
however, when they delete row i get Ref error. lets say if they deleted one row, let say they copy and paste one row to another spreadsheet. N after that, they delete the entire row. They get the Ref error, they re-drag again. Hence i will get Duplicate serial number.
Hence i tried typing the vba code as below.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer, y As Integer
i = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
y = i - 1
If Cells(i, 1) <> "" Then
Cells(i, 2).Value = Cells(y, 2).Value + 1
End If
End Sub
However, i encounter the following problem
1) Lets say if someone accidentally type in SOmethg in A10, B10 will have a number, however, if he delete the value in A10, B10 still have a value.

2) the numbering work if the person type in the value 1 by 1 in Column A, it does not work if he copy a range n paste. the Serial number onli appear in the last cell

3) If he drag in Column A, the Serial number onli appear in the last cell

Anyone can advice?