Solved

Excel Transpose Macro

Posted on 2004-09-24
4
941 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
  • 2
  • 2
4 Comments
 

Expert Comment

by:EliasSportsBureau
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

771 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

12 Experts available now in Live!

Get 1:1 Help Now