Transpose Columns to Rows (automatically inserts new rows)

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Transpose Columns to Rows (automatically inserts new rows)

  1. #1
    New Member
    Join Date
    Apr 2012
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post 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 file 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 use ASAP Utilities to 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.

  2. #2
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    15,473
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

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

    Try this...

    Code:
    Sub Transpose_Sports()
        Dim v As Variant, i As Long, j As Long
        Application.ScreenUpdating = False
        For i = Range("B" & Rows.Count).End(xlUp).Row To 2 Step -1
            With Range("B" & i)
                If InStr(.Value, ";") Then
                    v = Split(.Value, ";")
                    .Offset(1).Resize(UBound(v)).EntireRow.Insert
                    For j = 0 To UBound(v)
                        .Offset(j).Value = Trim(v(j))
                    Next j
                Else
                   .Value = Trim(.Value)
                End If
            End With
        Next i
        Application.ScreenUpdating = True
    End Sub
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  3. #3
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    15,473
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

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

    Note: 20K rows may take a couple of minutes to transpose. Also, If you have Excel 2003 or earlier, there is a limit of 65536 rows. You cannot transpose 20K rows to more than 65536 rows.
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  4. #4
    Board Regular jeffreybrown's Avatar
    Join Date
    Jul 2004
    Posts
    5,118
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Jeff

  5. #5
    New Member
    Join Date
    Apr 2012
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Thank you Help is greatly appreciated!

  6. #6
    New Member
    Join Date
    May 2016
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Hi, another noob here...
    Firstly, a lot of thanks for posting the question, also great help for the solution.

    The above solution answers partially to my problem:
    - I would like to copy the text in columns A, C, D and E and paste in the added rows (if any). Also, in my case, the number of columns is 10 i.e. goes up to column K (unlike the example by OP which has 5 columns)
    - In my case, as the OP, the column which requires transpose into rows is only one, the difference is that the rest of the data is to be duplicated.

    I believe it should not be a big deviation from the above solution, but I don't know how to modify the above solution to fit my needs.
    Any help is welcome.

    I'm using Excel 2007 on Windows 7. I can also get access to Excel 2016 on Win 7, if needed.

    P.S. English is not my first language, so apologies in advance for any mistakes.

  7. #7
    Board Regular
    Join Date
    Dec 2011
    Location
    The Netherlands
    Posts
    2,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    @ whataremacros

    Make you own question on this forum.

    Add a link to this question if you think it is usefull.

    Add a small part of your data on the forum and please also add the expected result.

  8. #8
    New Member
    Join Date
    May 2016
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

      
    Thanks.
    I have done that on this link. Along with sample data.

    I posted here initially because I wasn't clear on whether to make a new thread or to reply on an old one.

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com