Solved Transpose Data in Rows into Columns Microsoft Excel

January 27, 2013 at 19:35:11
Specs: Windows 7

Transpose Data in Rows into Columns Microsoft Excel??

I need code Visual Basic for transpose data in Rows into Columns....

Example

Assume that the values ​​in the columns A B C D E F G H

01 02 03 04 05 06 07 08
09 10 11 12 13 14 15 16

I need the data....

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16

thanks for your help.......


See More: Transpose Data in Rows into Columns Microsoft Excel

Report •


#1
January 27, 2013 at 20:30:48

Is your data really (and always) in Columns A-H or was that just an example?

The reason I ask is that the code is fairly simple if the layout is in that exact range but gets a little more complicated if you will have a different number of columns each time the code is run.

Another question: Will each Row always have the same number of Columns?

Finally, where do you want the transposed data to be placed?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
January 28, 2013 at 08:54:29

The number of columns is the same, and is always located in the same columns .....

Thanks ...


Report •

#3
January 28, 2013 at 12:47:31
✔ Best Answer

You didn't answer my last question:

Finally, where do you want the transposed data to be placed?

This code assumes the data is in Sheet1 and will be Copy/Transposed into Sheet2 Column A.

Modify as required...

Sub PasteTransposed()
'Determine last Source Row on Sheet1
    last_srcRw = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
'Initialize Destination Row variable
    dstRw = 1
'Loop through Source Rows
   For srcRw = 1 To last_srcRw
'Copy Row
      Sheets("Sheet1").Range("A" & srcRw & ":H" & srcRw).Copy
'PasteSpecial Transposed to Sheet2 Column A
      Sheets("Sheet2").Range("A" & dstRw).PasteSpecial Transpose:=True
'Increment Destination Row by 8
        dstRw = dstRw + 8
   Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
January 28, 2013 at 19:19:59

Goodnight

It works perfectly. Thank you very much, but I wanted to ask, there is another different method that does not involve select, copy paste?

Thank you friend


Report •

#5
January 28, 2013 at 19:42:07

Well, first off, you'll notice that the code does not "select" any ranges. It does a direct copy and then a paste. Using .Select is usually an inefficient way to write code.

Another method would be to loop through each cell and set a given cell on Sheet2 to a given cell on Sheet1. However, that is also very inefficient.

Why are you interested in a non-copy/paste method?

This isn't a homework assignment is it?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
January 28, 2013 at 19:53:02

Not for task, I am a civil engineer, i need help with the generation of this code, and I could not do without a copy and paste, and I'm interested in reality a code a little more efficient, and handling large amounts of data.

I appreciate if you can help me.


Report •

#7
January 29, 2013 at 09:41:28

I don't know that there is a more efficient way than Copy/Paste. That method is certainly more efficient than looping through each cell and setting one cell equal to another one.

The only other option I can think of is to use a macro to place formula's in the cells on Sheet2 which refer back to the cells on Sheet1. If the layout of the data was consistant, that macro could be run once to populate Sheet2 with the formulas and then the formulas would do their own thing from that point on.

However, I did add some code to my original macro that might help. In this version I turned off ScreenUpdataing so that you won't see the Copy/Paste operation. Everything will be done in the background and VBA will return control to the user once all of the data has been transposed. That will be a bit more efficient than watching the "large amounts of data" being transposed to Sheet2.

Sub PasteTransposed()
'Turn off Screen Updating
 Application.ScreenUpdating = False
'Determine last Source Row on Sheet1
    last_srcRw = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
'Initialize Destination Row variable
    dstRw = 1
'Loop through Source Rows
   For srcRw = 1 To last_srcRw
'Copy Row
      Sheets("Sheet1").Range("A" & srcRw & ":H" & srcRw).Copy
'PasteSpecial Transposed to Sheet2 Column A
      Sheets("Sheet2").Range("A" & dstRw).PasteSpecial Transpose:=True
'Increment Destination Row by 8
        dstRw = dstRw + 8
   Next
'Turn On Screen Updating
 Application.ScreenUpdating = = True
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
February 3, 2013 at 11:03:01

Thanks, friend!!!! I've been a big help

Report •


Ask Question