Come convertire una row di dati in più righe in Excel

Come faccio a prendere una row di dati in Excel e dividerlo in più righe?

Dato questo input:

Name Award Type 1 Amount 1 Award Type 2 Amount 2 Dan Top Performsr $5,000 Top Region $10,000 Kathy Rep Planmaker $2,500 

Voglio convertirlo a questo:

 Name Award Type Amount Dan Top Performsr $5,000 Dan Top Region $10,000 Kathy Rep Planmaker $2,500 

In parole: poiché la row "Dan" nei dati di input mostra due premi – uno nelle colonne B & C e un altro nelle colonne D & E – voglio dividere i dati di Dan in due righe, uno per each premio.

 Sub convert() Dim intcount As Integer intcount = 1 For i = 2 To **123** For j = 0 To 1 If ThisWorkbook.Sheets("sheet1").Cells(i, 1 + 2 * j).Value <> vbNullString Then intcount = intcount + 1 ThisWorkbook.Sheets("sheet1").Cells(i, 1).Copy Destination:=ThisWorkbook.Sheets("sheet2").Cells(intcount, 1) ThisWorkbook.Sheets("sheet1").Cells(i, 2 + 2 * j).Copy Destination:=ThisWorkbook.Sheets("sheet2").Cells(intcount, 2) ThisWorkbook.Sheets("sheet1").Cells(i, 3 + 2 * j).Copy Destination:=ThisWorkbook.Sheets("sheet2").Cells(intcount, 3) End If Next Next End Sub