Announcement

Collapse
No announcement yet.

Transpose Columns to Rows (automatically inserts new rows)

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Transpose Columns to Rows (automatically inserts new rows)

    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
    Last edited by kichkichkich; April 28th, 2012, 17:11. Reason: The inquiry is already been solved

  • kichkichkich
    replied
    Re: Transpose Columns to Rows (automatically inserts new rows)

    Thank you! This help is greatly appreciated! <3

    Leave a comment:


  • jindon
    replied
    Re: Transpose Columns to Rows (automatically inserts new rows)

    try
    Code:
    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

    Leave a comment:

Working...
X