Excel - A Macro to move data from rows to columns

Ask a question


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.

Excel - A macro copy to cell based on a specific criteria
Excel/VBA - Move all types of files