Transpose Columns to Rows (automatically inserts new rows)

[kichkichkich]

Hi, I am new here and complete amazed how macro's can make life so much easier but I just can't seem to understand the codes (no programming knowledge).


I would like to have the sports column to be transposed as rows. I have provided a sample https://skydrive.live.com/redir.aspx...150ED9D1FB!105


I can do the job manually by
1. Adjust the other columns (C D E) to about 20 columns away.
2. Use data to columns (delimited by ";").
3. Insert 20 additional rows in between the existing rows.
4. Copy the sports.
5. Special Paste> Transpose.
6. Move on to the next item and repeat 1-5.
7. After finishing, I just delete the empty rows.


The thing is my file is composed of almost 20k entries and repeating the process would take time.
Can anyone help me with this matter?
It will be surely appreciated. Thank you.

As a compliance with Rule. 5, Here are the link of other forums, which I posted the same question.

http://www.mrexcel.com/forum/showthr...19#post3135419
http://www.excelforum.com/excel-prog...94#post2774094

[jindon]

try

Sub test() 
    Dim a, b, i As Long, ii As Long, e, n As Long 
    a = Sheets(1).Range("a1").CurrentRegion.Value 
    Redim b(1 To 5000, 1 To UBound(a, 2)) 
    For i = 2 To UBound(a, 1) 
        n = n + 1 
        For ii = 1 To UBound(a, 2) 
            b(n, ii) = a(i, ii) 
        Next 
        For Each e In Split(a(i, 2), ";") 
            b(n, 2) = Trim$(e) 
            n = n + 1 
        Next 
        n = n - 1 
    Next 
    With Sheets(2).Cells(1).Resize(, UBound(a, 2)) 
        .Value = a 
        With .Offset(1).Resize(n) 
            .Value = b 
            .Columns.AutoFit 
        End With 
    End With 
End Sub 

[kichkichkich]

Thank you! This help is greatly appreciated! <3