Excel - A Macro to move data from rows to columns

April 2017



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.

Related


Published by aakai1056. Latest update on November 21, 2011 at 11:37 AM by aakai1056.
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).