Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel Transpose Macro

Posted on 2004-09-24
4
Medium Priority
?
949 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 400 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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

721 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