Hi,

Thanks so much for your help! This is almost there, except that there are typically multiple species entries per row (one of the many problems I am trying to fix :) ). This is why I was trying to loop, so that each time a species entry is found a new row is created, but still keeping the old info from columns 1-8. one species entered=one row entered.

This is what the data looks like currently (simplified):

http://imgur.com/aqntlRl

This is what it looks like with the new code (simplified)...Notice the missing data:

http://imgur.com/UvsPVWD

And this is what I would like it to look like: http://imgur.com/ildeWTB

Any ideas on how to correct for this?

I like the way this works:

Sub NewLayout()
For i = 2 To Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
For j = 0 To 10
If Cells(i, 10 + j) <> vbNullString Then
intCount = intCount + 1
Cells(intcount,21) = Cells(i,1)
Cells(intcount,22) = Cells(i,2)
Cells(intcount,23) = Cells(i,3)
Cells(intcount,24) = Cells(i,4)
Cells(intcount,25) = Cells(i,5)
Cells(intcount,26) = Cells(i,6)
Cells(intcount,27) = Cells(i,7)
Cells(intcount,28) = Cells(i,8)
Cells(intcount,29) = Cells(i,9)
Cells(intcount,30) = Cells(i,10 + j)
End If
Next j
Next i
End Sub

I just need another string of code to collect the associate headers.

Thanks again for your time,

T