Solved

Excel Transpose Macro

Posted on 2004-09-24
4
948 Views
Last Modified: 2008-02-01
Hi, I need a macro within Excel that will read in an entire workbook of data and transpose it to be vertical within the "A" column.

For example:

Current spreadsheet
apple  pear  fruit
apple  pear  fruit
....

I need it to be formatted
apple
pear
fruit
apple
pear
fruit
...

The spreadsheet is 3 columns by 1,973 rows but if it could be dynamic or ask how many columns and how many rows to transpose that would be terrifc.  Thank you
0
Comment
Question by:xcusmwah
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 

Expert Comment

by:EliasSportsBureau
ID: 12146671
This is assuming that you have a sheet in your workbook named "Sheet2" where your results will be posted to.

Sub Translate()

    Dim lngRow As Long
    Dim rngRow As Range
    Dim rngCol As Range

    Set rngRow = Range("A1")
    Set rngCol = Range("A1")
   
    lngRow = 1
   
    Do While rngRow.Value <> ""
       
        Do While rngCol.Value <> ""
            Sheet2.Cells(lngRow, 1) = rngCol.Value
           
            Set rngCol = rngCol.Offset(0, 1)
            lngRow = lngRow + 1
        Loop
       
        Set rngRow = rngRow.Offset(1, 0)
        Set rngCol = rngRow
    Loop
   
End Sub
0
 

Author Comment

by:xcusmwah
ID: 12147410
Hi EliasSportsBureau-

Thanks for your help.  The values within my spreadsheet are unique so it appears to be duplicating the B & C columns it reads in.

For example,

A         B        C- column headers
apple  pear  cherry
fig      twig   tree
pig     dog    cat


Should be

apple
pear
cherry
fig
twig
tree
pig
dog
cat

It should baiscally just read in the rows for a, b and c and shift the data into column a.  Can you still help me with it please?  Thanks again
0
 

Accepted Solution

by:
EliasSportsBureau earned 100 total points
ID: 12147469
I'm not exactly sure what you mean.  I put the data on "Sheet1" and then ran the macro and the data appeared the way you described it above in "Sheet2" all in Column A.

Hope this helps
0
 

Author Comment

by:xcusmwah
ID: 12147597
Hi EliasSportsBurea- your right, it does work.  I didn't have a "Sheet1" so I put my data on it and had it transfer over to Sheet2.  I did have to do a tweak for "Sheet2.Cells(lngRow, 1) = rngCol.Value", I made it "Sheet1.Cells(lngRow, 1) = rngCol.Value" and it ran perfectly.  Thanks again for all of your help.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month11 days, 5 hours left to enroll

631 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