Excel - A Macro to move data from rows to columns

November 2016



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 :

This document entitled « Excel - A Macro to move data from rows to columns » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.