Serial No will follow row number

Hi, I need some assistance here. I have a list of Data and the data are as follows

ID Name D.O.B
1 Bob 01/02/1999
2 Tom 03/08/2000
3 Ben 04/09/2000
4 Paul 04/07/2001

And when I copy one of the data (e.g. row 2) over to a new sheet, it works. But when I create a new entry, the data will be like

ID Name D.O.B
1 Bob 01/02/1999
3 Ben 04/09/2000
4 Paul 04/07/2001
4 Jen 05/09/2001

The ID will follow the row number instead

I want the end results to be
ID Name D.O.B
1 Bob 01/02/1999
3 Ben 04/09/2000
4 Paul 04/07/2001
5 Jen 05/09/2001


Below is my code

Private Sub CB_CreateStudent_Click()

Dim aRow As Long
Dim wsht As Worksheet
Set wsht = Worksheets("Data")

Dim rng As range
Set rng = Worksheets("Data").range("A:A")
Cnt = WorksheetFunction.Count(rng)

'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

wsht.Cells(aRow, 1).Value = Cnt + 1
wsht.Cells(aRow, 2).Value = Me.textbox_name
wsht.Cells(aRow, 3).Value = Me.textbox_DOB

End Sub

Hi
try so

Cnt = WorksheetFunction.Max(rng)

Originally Posted by nilem

Hi
try so

Cnt = WorksheetFunction.Max(rng)

Hi, I would like to add on a question, may I know how to highlight a new entry row after based on the D.O.B year. Example year 1999, will be blue, year 2000 will be green and so on.

Hi, it works perfectly.. Thank You