No announcement yet.

Transpose Columns to Rows (automatically inserts new rows)

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

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

    Thank you! This help is greatly appreciated! <3

    Leave a comment:

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

    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)
        For Each e In Split(a(i, 2), ";")
            b(n, 2) = Trim$(e)
            n = n + 1
        n = n - 1
    With Sheets(2).Cells(1).Resize(, UBound(a, 2))
        .Value = a
        With .Offset(1).Resize(n)
            .Value = b
        End With
    End With
    End Sub

    Leave a comment: