Stacking many columns to one big column

[dilettante]

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 ...


I googled and googled but found no similar question.

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

Can any of you talented people please help with the coding for this?

 

[Dryver14]

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

 

[circledchicken]

Hi and welcome to the forum,

Perhaps try this formula and drag down 2904 rows:

=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.

 

[AD_Taylor]

Or for columns of any size,

Make a selection first and then run the macro

Sub IntoOneColumn()
    Dim wsAdd       As Worksheet
    Dim rngDest     As Range
    
    Dim rng         As Range
    Dim area        As Range
    Dim col         As Range
    
    Set rng = Selection
    Set wsAdd = ThisWorkbook.Worksheets.Add
    
    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
            
            col.Copy wsAdd.Range(rngDest, rngDest.Resize(col.Cells.Count, 1))
        Next col
    Next area
End Sub

 

[dilettante]

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

Sub IntoOneColumn()
    Dim wsAdd       As Worksheet
    Dim rngDest     As Range
    
    Dim rng         As Range
    Dim area        As Range
    Dim col         As Range
    
    Set rng = Selection
    Set wsAdd = ThisWorkbook.Worksheets.Add
    
    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
            
            col.Copy wsAdd.Range(rngDest, rngDest.Resize(col.Cells.Count, 1))
        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:

=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.

 

[West Man]

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