Вот вам неймется :) пока с работы ехал еще вопрос придумали!
Пробуйте это:
Sub Macros()
Dim i, j, counter As Integer
counter = 1
i = 1
While Sheets("Лист1").Cells(i, 1) <> ""
j = 2
While Sheets("Лист1").Cells(i, j) <> ""
Sheets("Лист2").Cells(counter, 1) = Sheets("Лист1").Cells(i, 1) & Sheets("Лист1").Cells(i, j)
counter = counter + 1
j = j + 1
Wend
i = i + 1
Wend
End Sub
UpD:
Sub Macros()
Dim i, j, counter As Integer
counter = 1
i = 1
While Sheets("11").Cells(i, 1) <> ""
j = 2
While Sheets("11").Cells(i, j) <> "Конец"
If Sheets("11").Cells(i, j) = "" Then
j = j + 1
Else
Sheets("22").Cells(counter, 1) = Sheets("11").Cells(i, 1)
Sheets("22").Cells(counter, 2) = Sheets("11").Cells(i, j)
counter = counter + 1
j = j + 1
End If
Wend
i = i + 1
Wend
End Sub