# Stacking many columns to one big column

#### dilettante

##### New Member
Hi guys. Been reading this forum for many months now, you guys are awesome.

I was wondering if anybody could help me stack the many columns I have into one big column?

Example, from this:

------A--------B--------C------- ....... MY

1---CAT1-----DOG1----BIRD1
2---CAT2-----DOG2----BIRD2
3---CAT3-----DOG3----BIRD3
4---CAT4-----DOG4----BIRD4
.
.
8
(363 Columns of 8 cells)

To this (in this exact order)

-----A
1---CAT1
2---CAT2
3---CAT3
4---CAT4
5---DOG1
6---DOG2
7---DOG3
8---DOG4
9---BIRD1
10--BIRD2
11--BIRD3
12--BIRD4
.
.
.

2904 ...

There are 363 columns of 8 cells. Once stacked there will be 1 column of 2904 cells.

Last edited:

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Code:
``````Sub Stacking()
Dim x As Long
Dim y As Long
Dim NextRow As Long
NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
For x = 1 To 363
Cells(1, x).Resize(8).Copy Cells(NextRow, 1)
NextRow = NextRow + 8
Next x

End Sub``````

Hi and welcome to the forum,

Perhaps try this formula and drag down 2904 rows:

Code:
``````=INDEX(
[B]\$A\$1:\$MY\$8[/B],
MOD(ROWS(A\$1:A8), 8) + 1,
ROUNDUP(ROWS(A\$1:A1) / 8, 0)
)``````
Change \$A\$1:\$MY\$8 to your actual 8 x 363 range.

Or for columns of any size,

Make a selection first and then run the macro

Code:
``````Sub IntoOneColumn()
Dim rngDest     As Range

Dim rng         As Range
Dim area        As Range
Dim col         As Range

Set rng = Selection

For Each area In rng.Areas
For Each col In area.Columns
Set rngDest = wsAdd.Range("A" & Rows.Count).End(xlUp)
If rngDest.Row <> 1 Then
Set rngDest = rngDest.Offset(1, 0)
End If

Next col
Next area
End Sub``````

Code:
``````Sub Stacking()
Dim x As Long
Dim y As Long
Dim NextRow As Long
NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
For x = 1 To 363
Cells(1, x).Resize(8).Copy Cells(NextRow, 1)
NextRow = NextRow + 8
Next x

End Sub``````

Or for columns of any size,

Make a selection first and then run the macro

Code:
``````Sub IntoOneColumn()
Dim rngDest     As Range

Dim rng         As Range
Dim area        As Range
Dim col         As Range

Set rng = Selection

For Each area In rng.Areas
For Each col In area.Columns
Set rngDest = wsAdd.Range("A" & Rows.Count).End(xlUp)
If rngDest.Row <> 1 Then
Set rngDest = rngDest.Offset(1, 0)
End If

Next col
Next area
End Sub``````

Wow thank you guys very much. circledchicken's one seems the simplest for my case but others will find it useful.

Hi and welcome to the forum,

Perhaps try this formula and drag down 2904 rows:

Code:
``````=INDEX(
[B]\$A\$1:\$MY\$8[/B],
MOD(ROWS(A\$1:A8), 8) + 1,
ROUNDUP(ROWS(A\$1:A1) / 8, 0)
)``````
Change \$A\$1:\$MY\$8 to your actual 8 x 363 range.

Thank you. I'm having trouble making this work though. I'm not very good on excel. I tried copy-pasting this to a cell and it didnt work. Your "A\$1:\$MY\$8" range was spot on.

Assuming your data starts in sheet1A1, in sheet2A1 and dragged down:
=OFFSET(Sheet1!\$A\$1,MOD(ROW()-1,8),INT((ROW()-1)/8))

Replies
0
Views
417
Replies
3
Views
2K
Replies
8
Views
775
Replies
1
Views
149
Replies
3
Views
226

1,207,014
Messages
6,076,152
Members
446,187
Latest member
LMill

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back