Excel column containing unique ID number

Question

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.

Answer 1

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: