With your data starting from cell A1; try the below macro...with a sample..



Sub Macro()
Dim lngRow As Long, lngCol As Long, lngLastRow As Long

lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row + 2
lngRow = 1

Do While Trim(Cells(lngRow, 1)) < ""
lngCol = 2
Cells(lngLastRow, 1) = Cells(lngRow, 1)
Do While Trim(Cells(lngRow, lngCol)) < ""
Cells(lngLastRow, 2) = Cells(lngRow, lngCol)
lngCol = lngCol + 1
lngLastRow = lngLastRow + 1
Loop
lngRow = lngRow + 1
Loop

End Sub

If this post helps click Yes
---------------
Jacob Skaria


"zorakramone" wrote:

Hi

im trying to write a macro that will allow me to automat, inserting
rows based on the number of columns filled by names, then transpose
the names into the rows created.

E.g. from this...

Dave Peter Susan Luke Sam
Bob Brad Pedro
Joanna Pedro Danielle Jim


to this....

Dave Peter
Susan
Luke
Sam
Bob Brad
Pedro
Joanna Pedro
Danielle
Jim

any help would be kindly appreciated