This works for me with the sample data

Sub trans()
Set rng = Range("A1:F2") 'The input range: adjust as needed
Sheets("Sheet2").Select 'delete this line is transposing
to same sheet
Range("A10").Select 'The start of output range:
adjust as needed
k = 0

For Each rw In rng.Rows

For Each mycell In rw.Columns
If IsEmpty(mycell) Then Exit For
If mycell.Column = 1 Then
myletter = mycell
Else
ActiveCell.Offset(rowOffset:=k, columnOffset:=0) = myletter
ActiveCell.Offset(rowOffset:=k, columnOffset:=1) = mycell
k = k + 1
End If
Next

Next

End Sub

best wishes


--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

wrote in message
ups.com...
i have multiple rows, each with varing numbers of columns. i need to
get it so that each columns has it own row. i have over 1000 columns
and was hoping to get a macro can transpose the data for me?? for
example:

I have something like this:
A 3 4 7
B 5 2 9 1 5

I need it to look like this:
A 3
A 4
A 7
B 5
B 2
B 9
B 1
B 5

can someone help me?! =o)