Excel column containing unique ID number

user_9830821 0
Viewed 42

I'm trying to create an excel spreadsheet that will contain a unique number (ID) that's automatically assigned to each row.

Unique ID Some text
1 abc
2 def
3 ghi
4 jkl

Is it possible to get the following result when a new row is inserted between existing rows (in this example: between entry 2 and 3)? The existing IDs should not be changed, and a new ID should be assigned to the new row.

Unique ID Some text
1 abc
2 def
5 this is the new row
3 ghi
4 jkl

Are macros the best way to do this?
As far as I know, if formulas are used then they will not be automatically copied a newly inserted row.

excel
1 Answers

This answer is certainly not perfect, but it's a good start:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column = 2 Then
  Target.Offset(0, -1).Value = WorksheetFunction.Max(Range("A:A")) + 1
  End If
End Sub

It means that:

  • If you change something in your sheet
    Private Sub Worksheet_Change(ByVal Target As Range)
  • If you change it in the second column ("B")
    If Target.Column = 2 Then
  • Take the cell in the first column (the one to the left)
    Target.Offset(0, -1)
  • Put there the maximum value of the entire "A" column plus one:
    WorksheetFunction.Max(Range("A:A")) + 1

This is the way to add this:

enter image description here

user_42791550
Related