Sub Xpose()
Dim LR As Long, LC As Long, i As Long
LR = Columns("A").Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
Columns("A").Insert
For i = 1 To LR
LC = Rows(i).Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column - 1
Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(LC).Value = Application.Transpose(Range("B" & i).Resize(, LC))
Next i
End Sub
Oh No! Can't it possible with Excel? I'm weak @ VBA
Sub test()
n1 = Cells(Rows.Count, 1).End(xlUp).Row
n2 = Cells(1, Columns.Count).End(xlToLeft).Column
n = n1 * n2
ReDim vett(n) As String
For r = 1 To n1
n3 = Cells(r, Columns.Count).End(xlToLeft).Column
For c = 1 To n3
vett(j) = Cells(r, c)
j = j + 1
Next c, r
For i = LBound(vett) To UBound(vett)
Cells(i + 1, 1) = vett(i)
Next
Columns("B:C").ClearContents
End Sub