Split rows into columns in Excel

Posted on 2012-08-29
Last Modified: 2012-08-29
Using the attached file as an example.  I need the data currently in rows (Sheet1) to be all in columns (Sheet2).  I'm assuming that I can use a macro to accomplish this but have no idea how to do this.  Any help would be appreciated.
Question by:rkeith2412
    LVL 8

    Assisted Solution

    by:Elton Pascua
    You can transpose the data. Highlight A1 to E1 then copy. In Sheet2, right-click on A1 then Paste Special. Put a check on Transpose. Click OK. You can do the same for A2 to E2.
    LVL 31

    Accepted Solution

    A little VBA routine to do the trick.

    Sub Transpose()
    R = 1
    For Each Cell In Selection
        SrcVal = Cell.Value
        DestRng = "Sheet2!A" & R
        Range(DestRng) = SrcVal
        R = R + 1
    Next Cell
    End Sub

    Open in new window

    Select the area required to transpose before running routine. Adjust sheet name in DestRng as required.

    Rob H
    LVL 5

    Author Closing Comment

    techfanatic got me going in the right direction, but robhenson hit the nail on the head.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    I've recently been in need of an Excel macro that could add a letter before the text on multiple cells in an Excel document. My English is as it is, so I will try explain what it does diffrently. If you have an excel document with 2000 rows an…
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now