Copy/Pasting Columns to Bottom first Column

[lauwef]

Hi there,

I've got a large excel file consisting of about 1000 columns, each with 40 rows. What I want to do is putting all those values into one (for instance) the first column, without having to copy paste every other column manually to the bottom of the first column.

so

3-3-3
4-5-6
7-8-9
should become
3
4
7
3
5
8
3
6
9
Is there some kind of easy macro I can run to do this?
Thanks!

 

[MrKowz]

Give this a shot:

Public Sub ConsolidateColumns()
Dim LR  As Long, _
    LC  As Long, _
    i   As Long
    
LC = Cells(1, Columns.Count).End(xlToLeft).Column
Application.ScreenUpdating = False
For i = 2 To LC
    LR = Range("A" & Rows.Count).End(xlUp).row + 1
    Range(Cells(1, i), Cells(Rows.Count, i).End(xlUp)).Cut Destination:=Cells(LR, 1)
    Application.StatusBar "Currently on Column " & i
Next i
Application.ScreenUpdating = True
Application.StatusBar = False
End Sub

 

[lauwef]

Sweet, thanks man!

Only the statusbar line didn't work.

I'm going to be a pain, and extend my question a bit further;
- Every column contains 36 rows of data
- However, some of those columns contain missing values on the bottom rows
--> this causes the next column to be paste directly underneath it.
- I'd like to let it copy/paste every column underneath the first column, but at least 36 cells below the position on which the last column was pasted. Still get me?

So this:

3-3-3-3
4-5-6-7
2- -7-8
1- -3-4

Will ook like this:

3
4
2
1
4
5


3
6
7
3
3
7
8
4

If it helps, I can put a column counter in the first column running from 1-36 vertically.
thanks again!

 

[MrKowz]

Try:

Public Sub ConsolidateColumns()
Dim LC  As Long, _
    i   As Long
 
LC = Cells(1, Columns.Count).End(xlToLeft).Column
Application.ScreenUpdating = False
For i = 2 To LC
    Range(Cells(1, i), Cells(36, i)).Cut Destination:=Cells((36 * (i - 1)) + 1, 1)
    Application.StatusBar = "Currently on Column " & i
Next i
Application.ScreenUpdating = True
Application.StatusBar = False
End Sub

 

[lauwef]

Sheer brilliance, thanks a lot sir