Sub dienso()
Dim arr, arr1, s As String
Dim a As Long, i As Long, j As Long, lr As Long, b As Integer, c As Long, k As Long
With Sheet1
lr = .Range("C" & Rows.Count).End(xlUp).Row
If lr < 7 Then Exit Sub
arr = .Range("A4:k" & lr).Value
ReDim arr1(1 To UBound(arr, 1) + 1000, 1 To UBound(arr, 2))
For i = 1 To 4
a = a + 1
For j = 1 To UBound(arr, 2)
arr1(a, j) = arr(i, j)
Next j
Next i
For i = 5 To UBound(arr, 1)
b = arr(i, 4) - arr(i - 1, 4)
If b = 1 Then
a = a + 1
For j = 1 To UBound(arr, 2)
arr1(a, j) = arr(i, j)
Next j
ElseIf b > 1 Then
c = c + b - 1
For k = 1 To b - 1
If s = Empty Then s = (arr1(a, 4) + 1) Else s = s & ";" & (arr1(a, 4) + 1)
a = a + 1
arr1(a, 3) = arr1(a - 1, 3)
arr1(a, 4) = arr1(a - 1, 4) + 1
arr1(a, 6) = "H" & ChrW(7911) & "y"
Next k
a = a + 1
For j = 1 To UBound(arr, 2)
arr1(a, j) = arr(i, j)
Next j
End If
Next i
End With
With Sheet2
lr = .Range("C" & Rows.Count).End(xlUp).Row
If lr > 5 Then .Range("A6:K" & lr).ClearContents
If a Then .Range("A6").Resize(a, 11).Value = arr1
.Range("M8").Value = c
.Range("N8").Value = s
.Range("m9").Value = i - 4
End With
End Sub