Combining two columns into one - in sequence

[Power55]

Hi,


I have what I imagine is a fairly simple request however I can't seem to work it out.

Assume that the below table starts from cell A1.

[TABLE="width: 500"]
<tbody>[TR]
[TD]The[/TD]
[TD]cat[/TD]
[/TR]
[TR]
[TD]stole[/TD]
[TD]the[/TD]
[/TR]
[TR]
[TD]banana[/TD]
[TD]from[/TD]
[/TR]
[TR]
[TD]the[/TD]
[TD]tree[/TD]
[/TR]
</tbody>[/TABLE]



From the above table, I would like to combine the cells into one column so that my one column is as follows:

[TABLE="width: 500"]
<tbody>[TR]
[TD]The[/TD]
[/TR]
[TR]
[TD]cat
[/TD]
[/TR]
[TR]
[TD]stole[/TD]
[/TR]
[TR]
[TD]the[/TD]
[/TR]
[TR]
[TD]banana[/TD]
[/TR]
[TR]
[TD]from[/TD]
[/TR]
[TR]
[TD]the[/TD]
[/TR]
[TR]
[TD]tree[/TD]
[/TR]
</tbody>[/TABLE]


Do note that each word in the above is a separate cell on a separate row, I am not after them being all combined into one cell on different lines.

Thanks

 

[My Aswer Is This]

Combine them into what column?

And I assume you mean a sheet not a Table.

A Table and a Sheet are not the same.

 

[Power55]

Combine them into what column?

And I assume you mean a sheet not a Table.

A Table and a Sheet are not the same.

Combine them into a third column.

I used the table button when creating a thread to make it clear that the words were in different columns, it just didn't show up in the thread for some reason.

 

[Power55]

The stole banana and the would be the data in column A, cells A1 A2 A3 A4. Cat the from tree would be in B1 B2 B3 B4.

I would like them in one column as
A1
B1
A2
B2
A3
B3
etc

 

[My Aswer Is This]

Try this:

Sub Copy_To_Column_C()
'Modified  10/28/2018  11:41:53 PM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim x As Long
x = 1
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To Lastrow
    Cells(x, 3).Value = Cells(i, 1).Value: x = x + 1
    Cells(x, 3).Value = Cells(i, 2).Value: x = x + 1
Next
Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

Another way

Sub Two_to_One()
  With Range("A1", Range("A1").End(xlDown).Offset(, 1))
    Range("C1").Resize(.Cells.Count).Value = Application.Transpose(Split(Join(Application.Transpose(Evaluate(.Columns(1).Address & "&"" ""&" & .Columns(2).Address)))))
  End With
End Sub

 

[Power55]

Try this:

Sub Copy_To_Column_C()
'Modified  10/28/2018  11:41:53 PM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim x As Long
x = 1
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To Lastrow
    Cells(x, 3).Value = Cells(i, 1).Value: x = x + 1
    Cells(x, 3).Value = Cells(i, 2).Value: x = x + 1
Next
Application.ScreenUpdating = True
End Sub

Thanks!!!

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

Thanks!!!