Hi,
Try this as a starter. It works by assuming the data is always
numeric and the header is not - which I guess is probably wrong so you will
need to change the "loop until ..." test to determine the header. I still
wasn't sure from your reply how I would identify the header.

This code also assumes there are no blank rows between sets of data; again
if this isd not true, the logic will need to be changed.

If this doesn't work, send me a sample of your data.
)

HTH

Sub Rows2Columns()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim outrng As Range

Set ws1 = Worksheets("Sheet1") ' <==== change to your tab name (input)
Set ws2 = Worksheets("Sheet2") ' <==== Change to your tab name (output)
Set outrng = ws2.Range("a1")

With ws1
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
sr = 1
r = 2
Do While r < lastrow
Do
r = r + 1
' Tests for header being non-numeric == change to suit your data
' i.e. how do we know this a header row?
' and terminates on last cell being blank i.e. no embedded blanks in
list
Loop Until Not IsNumeric(.Cells(r, 1)) Or .Cells(r, 1) = ""

.Cells(sr, 1).Resize(r - sr, 1).Copy outrng
sr = r
r = r + 1
Set outrng = outrng.Offset(0, 1)
Loop

End With

End Sub

"Pich" wrote:


The header does not have a specific identifier, only its name. Their are
a couple of repeating headers with a various number of data rows.
Example:

Header1
Data1
Data2
Data3

Header2
Data1
Data2

Header3
Data1
Data2
Data3
Data4
Data5
Data6

Header1
Data1

Header2
Data1
Data2
Data3
Data4

Header3
Data1
Data2
Data3

and so on....


--
Pich
------------------------------------------------------------------------
Pich's Profile: http://www.excelforum.com/member.php...o&userid=29007
View this thread: http://www.excelforum.com/showthread...hreadid=487353