Solved

Excel Transpose Macro

Posted on 2004-09-24
4
946 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
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…
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…

730 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