Excel VBA assign unique ID to data set

[nitrammada]

Hi Folks,

I'm setting up a spread sheet that will ultimately have up to 10,000 records in it.
I need to assign a unique ID to each row. I don't want to use a formula with "+1" as I will have to delete rows and add rows from time to time and I need the ID to remain constant(unique) to that row of data. I don't want to use autofill and then cut and paste into an adjacent column. So i figure I will need to use VBA. Let's say my unique ID number is in column B, I need to be able to insert a row anywhere in my spread sheet and for a unique ID number to populate the cell in column B. The code will need to search for the highest ID number in column B (the ID will be a number) and then assign the next highest number in column B in the row I have just inserted. Does that make sense? Can anyone help me with this? I'm not very proficient with VB (dummy actually) so if you have any code I could use I would be most grateful. I'm using Windows 10, 64 bit

Thanks in advance

Adam

 

[MickG]

Glad you now have it working
Regrds Mick

 

[bavarian]

I am trying to make use of this solution and adjusted it the way I need it. Basically just making use of column A instead of column B.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
Application.EnableEvents = False
  Set Rng = Range("A2", Range("A" & Rows.Count).End(xlUp))
    If Target.Rows.Count = 1 Then
        Select Case True
            Case Target.Row <= Rng.Count: Cells(Target.Row, "A") = Application.Max(Rng) + 1
            Case Target.Row = Rng.Count + 1: Cells(Target.Row, "A") = Application.Max(Rng) + 1
            Case Cells(Target.Row - 1, "A") = Cells(Target.Row, "A") = Cells(Target.Row, "A") = Application.Max(Rng) + 1
        End Select
    End If
Application.EnableEvents = True

End Sub

It seems to work fine, but let me ask: how are things supposed to work when deleting a row? Here deleting a row makes the rows below the deleted row move upwards and receiving new numbers ...