Consolidation of columns

[Guddo]

Hi,

I want formula to bring sets of four columns into one set of long columns. I have 1000+ columns vertically located so want to bring in one series. Please assist.


Current layout

a b c d e f g h I j k l
a b c d e f g h I j k l
a b c d e f g h I j k l
a b c d e f g h I j k l

Desired layout

a b c d
a b c d
a b c d
a b c d
e f g h
e f g h
e f g h
e f g h
I j k l
I j k l
I j k l
I j k l


Thanks.

 

[Yongle]

Try this
after replacing Sheet99 with correct sheet name

Sub ConsolCols()
    Dim rng As Range, cCount As Long, c As Long, ws As Worksheet
    With Sheets("[COLOR=#ff0000]Sheet99[/COLOR]")
        Set ws = Sheets.Add
        Set rng = .Range("A1", .Range("A" & Rows.Count).End(xlUp)).Resize(, 4)
        c = .Cells(1, Columns.Count).End(xlToLeft).Column
        c = Round(c / 4, 0)
        For cCount = 0 To c
            rng.Offset(, cCount * 4).Copy ws.Cells(Rows.Count, 1).End(xlUp).Offset(1)
        Next cCount
    End With
End Sub

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​	L​	M​	N​	O​	P​	Q​	R​	S​	T​
1​	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
2​	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
3​	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T

Sheet: Sheet99

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​
1​
2​	A	B	C	D
3​	A	B	C	D
4​	A	B	C	D
5​	E	F	G	H
6​	E	F	G	H
7​	E	F	G	H
8​	I	J	K	L
9​	I	J	K	L
10​	I	J	K	L
11​	M	N	O	P
12​	M	N	O	P
13​	M	N	O	P
14​	Q	R	S	T
15​	Q	R	S	T
16​	Q	R	S	T

Sheet: SheetXX