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

<ElaineYKwan@gmail.com> wrote in message
news:1136400188.400198.15290@g49g2000cwa.googlegroups.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)
>