Joining Columns one below the other

[harshj]

Imagine I have some columns of data. How can I stack multiple columns on below the other in column A?

For example, A column is blank now. B,C,D have some data.

	A	B	C	D
1		ab	gh	ij
2		cd		kl
3		ef
4

<tbody>
</tbody>

Can all the columns be stacked in the empty column A?

	A
1	ab
2	cd
3	ef
4	gh
5	ij
6	kl
7

<tbody>
</tbody>

This is just an example. I have 8000 columns in reality. Please help me with this. Thank you!

 

[Hamburgler]

Here is a quick attempt:

Sub test()
    Dim x As Range, y As Range
    Set y = [A1]
    For Each x In [B1:D4]
        If Len(x.Value) > 0 Then
            Set y = y.Offset(1, 0)
            y.Value = x.Value
        End If
    Next x
End Sub

But it takes the values as they appear in rows, not columns...

ab

gh

ij <tbody> </tbody>

cd

kl

ef

<tbody>
</tbody>

<tbody>
</tbody>

 

[Hamburgler]

Here is code that will do it by column. You will need to change "[B1:D4]" to the actual address of your data. Also be careful that column "A" is empty or the data in it will be overwritten.

Sub test2()
    Dim x As Range, y As Range, z As Range
'   ------------------------------------------------------
    Set y = [A1]
    For Each z In [B1:D4].Columns
        For Each x In z.Cells
            If Len(x.Value) > 0 Then
                y.Value = x.Value
                Set y = y.Offset(1, 0)
            End If
        Next x
    Next z
End Sub

 

[CHinojosa]

Here is code that will do it by column. You will need to change "[B1:D4]" to the actual address of your data. Also be careful that column "A" is empty or the data in it will be overwritten.

Sub test2()
    Dim x As Range, y As Range, z As Range
'   ------------------------------------------------------
    Set y = [A1]
    For Each z In [B1:D4].Columns
        For Each x In z.Cells
            If Len(x.Value) > 0 Then
                y.Value = x.Value
                Set y = y.Offset(1, 0)
            End If
        Next x
    Next z
End Sub

How could I do this but link it to a column in a different workbook?