0
Thanks

A few words of thanks would be greatly appreciated.

Excel - A Macro to move data from rows to columns



Issue


I have data in a single column and would like to transpose it into a row for every three rows with a space delimiter, for example; The column of data varies in the amount of data each time.
A  
B  
C  
D  
E  
F  
G  
H  
I  
J  
etc 


Into

A B C  
D E F  
G H I  
J etc

Solution


Suppose the data are in A2 down with A1 having column headings.

Try this macro
  • The results will be in columns C to E


  
Sub test()  
Dim rng As Range, m As Integer, c As Range  
Columns("c:E").Delete  
m = 3  
Set rng = Range(Range("a2"), Range("a2").End(xlDown))  
Set c = Range("a2")  
Do While c <> ""  
'MsgBox c.Address  

Range(c, c.Offset(m - 1, 0)).Copy  
Cells(Rows.Count, "c").End(xlUp).Offset(1, 0).PasteSpecial Transpose:=True  
Set c = c.Offset(m, 0)  
Loop  


End Sub 


Thanks to Venkat1926 for this tip.
0
Thanks

A few words of thanks would be greatly appreciated.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jeff Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.
This document, titled « Excel - A Macro to move data from rows to columns », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).

0 Comments