Using VBA loops to turn a multiple row list into a single column

[eg11]

I am trying to figure out how I can create a macro to loop through my data that is in 6 rows to a single column with no repeating headers. my data is in the form the top part and I am trying to transform it into the bottom part of the image.

 

[alansidman]

Power Query Solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Column1"}, {{"Data", each _, type table [Column1=text, Column2=any]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Data],"Index",1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Data"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Column2", "Index"}, {"Custom.Column2", "Custom.Index"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Custom", List.Distinct(#"Expanded Custom"[Column1]), "Column1", "Custom.Column2"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Custom.Index"})
in
    #"Removed Columns1"

Book3
	A	B	C	D	E	F	G	H	I
1	Column1	Column2		row 1	row2	row 3	row 4	row 5	row 6
2	row 1	aaa		aaa	bbb	ccc	ddd	eee	fff
3	row2	bbb		11	22	33	44	55	66
4	row 3	ccc
5	row 4	ddd
6	row 5	eee
7	row 6	fff
8	row 1	11
9	row2	22
10	row 3	33
11	row 4	44
12	row 5	55
13	row 6	66
Sheet1

 

[Fluff]

With a formula

Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1	Column1	Column2		Row 1	Row 2	Row 3	Row 4	Row 5	Row 6
2	Row 1	aaa		aaa	bbb	ccc	ddd	eee	fff
3	Row 2	bbb		11	22	33	44	55	66
4	Row 3	ccc
5	Row 4	ddd
6	Row 5	eee
7	Row 6	fff
8	Row 1	11
9	Row 2	22
10	Row 3	33
11	Row 4	44
12	Row 5	55
13	Row 6	66
14
Main

Cell Formulas
Range	Formula
D1:I1	D1	=TOROW(UNIQUE(A2:A13))
D2:I3	D2	=WRAPROWS(B2:B13,6)
Dynamic array formulas.