Solved

Excel Transpose Macro

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

ScreenConnect 6.0 Free Trial

Check out the updates in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI that improves session organization and overall user experience. See the enhancements for yourself!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Copy a row 12 62
Protecting vb6 & .Net code Obfuscation 18 126
Using an encrypted  value to decrypt and display contents in vb6 9 52
fso.FolderExists("\\server\HiddenFolder$") 4 68
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…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

831 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