An alternative ...(but test first!) ...

Sub Transpose()

i = Cells(Rows.Count, "A").End(xlUp).Row
Do
n = 0
Do
n = n + 1
Loop While Cells(i - n, 1) <> "Start"

Set rng = Cells(i - n + 1, 1).Resize(n, 1)
rng.Copy
Cells(i - n, 2).Resize(1, n).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
rng.EntireRow.Select
Selection.Delete Shift:=xlUp
i = i - n - 1

Loop While i > 1
End Sub

HTH

"walkersc@gmail.com" wrote:

> I have a large file with many rows; let's say it looks like this:
> start
> data1
> data2
> data3
> data4
> start
> data1
> data2
> start
> data1
> data2
> data3
> ....
> What I want to do is transpose the column to a row, and then
> each time the word START appears, start a new row. So the above
> would look like this:
> start data1 data2 data3 data4
> start data1 data2
> start data1 data2 data3
> ....
>
> Any ideas?
>
> Thanks in advance,
> Scott
>
>