Need To Consolidate Multiple Columns Into One Stacked Column

[Gesquivel355]

Hey Everyone,

I have a data set of 100 columns by 300 columns. What I'm trying to do is stack all of the data from the columns (I don't care about the headings) into ONE consolidated column. Is there a formatting function I can use to do this? I tried throwing the data into a pivot table, but it's not "stacking" the data how I want. Below is a simple example of what I am trying to do.

I think this is an easy question that I can probably achieve without using a formula or having to copy/paste a million times. Thanks in advance for the help.

column 1 column 2
1 4
2 5
3 6

consolidated (stacked) column
1
2
3
4
5
6

 

[Rick Rothstein]

Give this macro a try...

Sub StackColumns()
  Dim X As Long, LastColumn As Long
  LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
  For X = 2 To LastColumn
    Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(Cells(Rows.Count, X).End(xlUp).Row).Value = Cells(1, X).Resize(Cells(Rows.Count, X).End(xlUp).Row).Value
  Next
  Columns("B").Resize(, LastColumn).Delete
End Sub

If you do not want to delete your original data, comment out the last statement.

 

[mirabeau]

Sub stack()
Dim e As Range
For Each e In Cells(1).CurrentRegion.Offset(, 1).Columns
    e.Cut Range("A" & Rows.Count).End(3)(2)
Next
End Sub

although fine-tuning, if any needed, depends on the details of your data layout.

 

[Gesquivel355]

Thanks, guys. Much appreciated.

 

[cyrilbrd]

am using this one:

Sub Stack()
Dim iRow, iCol, iTargetRow, iMaxRow As Long
iMaxRow = 40
iTargetRow = 1
Columns(18).Clear
For iCol = 5 To 16
For iRow = 1 To iMaxRow
'If Cells(iRow, iCol) > "" Then
Cells(iTargetRow, 18) = Cells(iRow, iCol)
iTargetRow = iTargetRow + 1
'Else: Exit For
'End If
Next
Next
End Sub

it is set to copy column E(5) to column P(16) into column R(18)