n = Sheets("Лист1").UsedRange.Column + Sheets("Лист1").UsedRange.Columns.Count - 1
For ii = 1 To n
R(ii) = Sheets("Лист1").Cells(Rows.Count, ii).End(xlUp).Row
Next

 
  Private Sub
  CommandButton1_Click()

  End Sub
  
  Private Sub UserForm_Initialize()
  Dim i, n As Integer
 
  n = Sheets("Лист1").Cells(1, 1).CurrentRegion.Rows.Count
      
  For i = 1 To n       
  Dim Mycmd As Control
  Dim Mycmd1 As Control
 
 Set Mycmd = Controls.Add("Forms.CheckBox.1")
 Set Mycmd1 = Controls.Add("Forms.TextBox.1")
 
              With Mycmd
         .Height = 18
         .Left = 10 
         .Top = i * 20
         .Width = 90
         .Caption = Sheets("Лист1").Cells(1, i).Value
         .Name = "CheckBox" & i
         .Value = "True"
              End With
 
              With Mycmd1
        .Height = 18
        .Left = 80
        .Top = i * 20
        .Width = 90
        .Value = i
        .Name = "TextBox" & i
              End With
  Next i
  End Sub
 

Private Sub CommandButton1_Click()
Dim Cl As Range, Cl1 As Range, Rng As Range, per As Long
Application.ScreenUpdating = False
per = UserForm1.TextBox1.Value
Set Rng = ActiveSheet.UsedRange

For Each Cl In Rng.Columns(1).SpecialCells(2)
    per = per + 1
    Cl.Offset(0, 1).Value = per

If Left(Cl, 6) = LastCl Then
        Cl.Offset(0, 4).Value = Cl.Offset(-1, 4).Value

        Else
        Cl.Offset(0, 4).Value = per
        LastCl = Left(Cl, 6)
    End If

Next
Application.ScreenUpdating = True
Unload Me
End Sub

Private Sub CommandButton1_Click() 
per = UserForm1.TextBox1.Value 

For n = 2 To i 
If IsEmpty(Cells(n, 1)) = False Then 
per = per + 1 
Cells(n, 21).Value = per 
Else 
Exit For 
End If 
Next n 

End Sub 

Private Sub CommandButton1_Click()
per = UserForm1.TextBox1.Value

For n = 2 To i
If IsEmpty(Cells(n, 1)) = False Then
per = per + 1
Cells(n, 21).Value = per
Else
Exit For
End If
Next n

End Sub