Running sub to reformat data onto sheet 2 really slow of get "Excel is not responding"

[Rolnam]

Hello!
Glad to have found this site. Once upon a time I was a programmer, but really didn't so anything with VBA in excel.
Unfortunately, I believe I am using an overly large hammer to address my challenge with minimal efficiencies.

I ran the code below on the first 1,000 rows in sheet(1) and it ran in around 3 minutes. Output was 1,311 rows in sheet(2). This is really slow.
I need to run this for 60,000 rows - it shouldn't take hours should it?

I am confident there is a more elegant solution.
NOTES:
1. the starting data is a list of time punches; 1 row per person with all their punches for the day. (in, breaks, meals, out)
2. I need the data to be in sheet 2 as one line per set of in /out punches. For most they are part time and just have in and out.
3. since the punch pairs are all left to right (pairs and then blanks) I can check for the blank field to know how many pairs I will be writing to sheet(2).

 

[Rolnam]

I shall experiment - thanks.

Victory! I didn't do the resize. and I had to prep columns D and E for the date format I needed in sheet(2), but it ran the whole thing in like 15 - 20 seconds.

 

[Rolnam]

The full edit, untested. I've tried to keep it as simple as possible so that you can hopefully follow it a bit easier.

Sub ConvertPunchRow()
    Application.ScreenUpdating = False
    Dim NewRow As Long, i As Long
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = ThisWorkbook.Worksheets(1)
    Set ws2 = ThisWorkbook.Worksheets(2)
    NewRow = 1
   
    For i = 2 To 1000
        If ws1.Cells(i, 5).Value = vbNullString Then
        ' Emp shift info
            ws2.Cells(NewRow, 1).Resize(, 4).Value = ws1.Cells(i, 1).Resize(, 4).Value
            ws2.Cells(NewRow, 5).Value = ws1.Cells(i, 13).Value
            NewRow = NewRow + 1
   
        ' = = = 1 Break  = = =
        ElseIf ws1.Cells(i, 7).Value = vbNullString Then
            ws2.Cells(NewRow, 1).Resize(, 5).Value = ws1.Cells(i, 1).Resize(, 5).Value
            ws2.Cells(NewRow + 1, 1).Resize(, 3).Value = ws1.Cells(i, 1).Resize(, 3).Value
            ws2.Cells(NewRow + 1, 4).Value = ws1.Cells(i, 6).Value
            ws2.Cells(NewRow + 1, 5).Value = ws1.Cells(i, 13).Value
            NewRow = NewRow + 2
               
        ' = = = 2 Breaks  = = =
   
        ElseIf ws1.Cells(i, 9).Value = vbNullString Then
            ws2.Cells(NewRow, 1).Resize(, 5).Value = ws1.Cells(i, 1).Resize(, 5).Value
            ws2.Cells(NewRow + 1, 1).Resize(, 3).Value = ws1.Cells(i, 1).Resize(, 3).Value
            ws2.Cells(NewRow + 1, 4).Resize(, 2).Value = ws1.Cells(i, 6).Resize(, 2).Value
            ws2.Cells(NewRow + 2, 1).Resize(, 3).Value = ws1.Cells(i, 1).Resize(, 3).Value
            ws2.Cells(NewRow + 2, 4).Value = ws1.Cells(i, 8).Value
            ws2.Cells(NewRow + 2, 5).Value = ws1.Cells(i, 13).Value
            NewRow = NewRow + 3
   
        ' = = = 3 Breaks  = = =
       
        ElseIf ThisWorkbook.Sheets(1).Cells(i, 11).Value = vbNullString Then
            ws2.Cells(NewRow, 1).Resize(, 5).Value = ws1.Cells(i, 1).Resize(, 5).Value
            ws2.Cells(NewRow + 1, 1).Resize(, 3).Value = ws1.Cells(i, 1).Resize(, 3).Value
            ws2.Cells(NewRow + 1, 4).Resize(, 2).Value = ws1.Cells(i, 6).Resize(, 2).Value
            ws2.Cells(NewRow + 2, 1).Resize(, 3).Value = ws1.Cells(i, 1).Resize(, 3).Value
            ws2.Cells(NewRow + 2, 4).Resize(, 2).Value = ws1.Cells(i, 8).Resize(, 2).Value
            ws2.Cells(NewRow + 3, 1).Resize(, 3).Value = ws1.Cells(i, 1).Resize(, 3).Value
            ws2.Cells(NewRow + 3, 4).Value = ws1.Cells(i, 10).Value
            ws2.Cells(NewRow + 3, 5).Value = ws1.Cells(i, 13).Value
            NewRow = NewRow + 4
       
        ' = = = 4 Breaks  = = =
        ElseIf ThisWorkbook.Sheets(1).Cells(i, 11).Value <> vbNullString Then
            ws2.Cells(NewRow, 1).Resize(, 5).Value = ws1.Cells(i, 1).Resize(, 5).Value
            ws2.Cells(NewRow + 1, 1).Resize(, 3).Value = ws1.Cells(i, 1).Resize(, 3).Value
            ws2.Cells(NewRow + 1, 4).Resize(, 2).Value = ws1.Cells(i, 6).Resize(, 2).Value
            ws2.Cells(NewRow + 2, 1).Resize(, 3).Value = ws1.Cells(i, 1).Resize(, 3).Value
            ws2.Cells(NewRow + 2, 4).Resize(, 2).Value = ws1.Cells(i, 8).Resize(, 2).Value
            ws2.Cells(NewRow + 3, 1).Resize(, 3).Value = ws1.Cells(i, 1).Resize(, 3).Value
            ws2.Cells(NewRow + 3, 4).Resize(, 2).Value = ws1.Cells(i, 10).Resize(, 2).Value
            ws2.Cells(NewRow + 4, 1).Resize(, 3).Value = ws1.Cells(i, 1).Resize(, 3).Value
            ws2.Cells(NewRow + 4, 4).Resize(, 2).Value = ws1.Cells(i, 12).Resize(, 2).Value
            NewRow = NewRow + 5
        End If
    Next i
Application.ScreenUpdating = True
End Sub

I will experiment with this after my deadline passes. thanks again.
I am guessing there is just so much overhead with using functions like Copy and "Destination" (Paste) and the behind the scenes code they use. Value = Value is much more straight forward.