Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 3 of 3

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

  1. #1
    Join Date
    12th March 2012
    Posts
    3

    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 at 17:11. Reason: The inquiry is already been solved

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,486

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

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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    12th March 2012
    Posts
    3

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

    Thank you! This help is greatly appreciated! <3

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 5
    Last Post: January 22nd, 2012, 02:59
  2. Transpose Rows To Columns
    By GoldRushGuru in forum EXCEL HELP
    Replies: 7
    Last Post: March 8th, 2011, 06:19
  3. Transpose each 4 rows into columns
    By stheelders in forum EXCEL HELP
    Replies: 5
    Last Post: February 25th, 2011, 21:55
  4. Transpose Data Columns To Rows Automatically
    By iggiziggi in forum EXCEL HELP
    Replies: 6
    Last Post: March 13th, 2008, 11:20
  5. Transpose Columns to Rows
    By dunno in forum EXCEL HELP
    Replies: 3
    Last Post: November 17th, 2006, 14:49

Bookmarks

Posting Permissions

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