Eu mesmo resolvi isso.
' Copy week & name to all rows
For i = 1 To ActiveSheet.UsedRange.Rows.Count - 1 Step 1
If IsEmpty(ActiveSheet.Range("A" & i + 1)) Then
ActiveSheet.Range("A" & i).Copy Destination:=ActiveSheet.Range("A" & i + 1) ' Week
End If
If IsEmpty(ActiveSheet.Range("B" & i + 1)) Then
ActiveSheet.Range("B" & i).Copy Destination:=ActiveSheet.Range("B" & i + 1) ' Name
End If
Next i
' Delete entire rows where there is no number (this emplies the row is invalid)
On Error Resume Next
ActiveSheet.Columns("E").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0