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.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]ab[/TD]
[TD]gh[/TD]
[TD]ij[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]cd[/TD]
[TD][/TD]
[TD]kl[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]ef[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]









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

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ab[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]cd[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]ef[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]gh[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]ij[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]kl[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ab[/TD]
[/TR]
[TR]
[TD]gh
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD]ij[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]cd[/TD]
[/TR]
[TR]
[TD]kl[/TD]
[/TR]
[TR]
[TD]ef[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 72"]
<tbody>[TR]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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?