Numbered column in vba

[snb]

You could use:

Sub snb()
  sn = Columns(1).SpecialCells(2)
    
  For j = 1 To UBound(sn)
    sn(j, 1) = UBound(Filter(Application.Transpose(sn), sn(j, 1))) + 1
  Next
    
  Columns(1).SpecialCells(2).Offset(, 3) = sn
End Sub