Option Explicit
Sub test()
Dim arr, i, p, m, r, s
With Sheets("sheet1")
arr = .Range("a1:c" & .Cells(Rows.Count, "b").End(xlUp).Row + 1).Value
End With
p = 2
For i = 2 To UBound(arr, 1) - 1
If Len(arr(i + 1, 1)) > 0 Or i = UBound(arr, 1) - 1 Then
m = m + 1
arr(m, 1) = arr(p, 1): arr(m, 2) = p: arr(m, 3) = i
p = i + 1
End If
Next
With Sheets("sheet2")
.Columns("c").Resize(, 9).Clear
s = .[b2].Value: r = 2
For i = 1 To m
If InStr(arr(i, 1), s) Then
Sheets("sheet1").Cells(arr(i, 2), "a").Resize(arr(i, 3) - arr(i, 2) + 1, 9).Copy .Cells(r, "c")
r = r + arr(i, 3) - arr(i, 2) + 1
End If
Next
End With
End Sub |