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
Suppose the data are in A2 down with A1 having column headings.
Try this macro
- The results will be in columns C to E
Dim rng As Range, m As Integer, c As Range
m = 3
Set rng = Range(Range("a2"), Range("a2").End(xlDown))
Set c = Range("a2")
Do While c <> ""
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)
Thanks to Venkat1926
for this tip.
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