Move some cells to next records and delete some rows

[YasserKhalil]

Hello everyone
In sheet1 I have some raw data that needs to be reformatted ..
In row1 to 5 there's no problem .. these rows will be kept as they are
After that specific string "Hello" will appear in the first column >> in this case the three following rows will be deleted

The most important part is that the next row of the string "Hello" would be moved to the next records (rows) till the string "Hello" be found again ...
I have attached in Sheet2 the desired output so as to be easier to get it

I need the output to be in sheet2 not in the same data sheet (Sheet1)
Note : the original data is about 10,000 rows so I am searching for faster way for this task

Thanks advanced for help

[mana]

Option Explicit


Sub test()
Dim v
Dim w(), m(1 To 5)
Dim i As Long, j As Long
Dim n As Long


v = Sheets("sheet1").Cells(1).CurrentRegion.Resize(, 12).Value
ReDim w(1 To UBound(v), 1 To 12)

For i = 1 To 5
For j = 1 To 12
w(i, j) = v(i, j)
Next
Next

n = i
For i = 6 To UBound(v)
If v(i, 1) = "Hello" Then
For j = 1 To 5
m(j) = v(i + 1, j)
Next
i = i + 3
Do
For j = 1 To 7
w(n, j) = v(i, j)
Next
For j = 8 To 12
w(n, j) = m(j - 7)
Next
n = n + 1
If i = UBound(v) Then Exit Do
If v(i + 1, 1) = "Hello" Then Exit Do
i = i + 1
Loop
End If
Next

With Sheets("sheet2").Cells(1).CurrentRegion
.ClearContents
.Resize(n - 1, 12).Value = w
End With

End Sub

[YasserKhalil]

Really amazing and awesome Mr. Mana
I have tried yesterday to achieve that using arrays but I couldn't figure it out as you did .. I was lost

Thank you very much for great help
Best Regards