Joint two column into one

[Vanda_a]

Dear all

Please kindly adjust my code to a better one. Esp the red part. I have no idea how to deal with selected cells or range

Sub Test()

Dim Row As Integer
Dim Col As Integer
Dim Cels As Range
Dim Rng As Range

Row = ActiveCell.Row
Col = ActiveCell.Column + 2
Set Rng = Range(Cells(ActiveCell.Row, ActiveCell.Column), Cells(ActiveCell.End(xlDown).Row, (ActiveCell.Column + 1))) ''In fact I want a selected range

For Each Cels In Rng
    Cells(Row, Col) = Cels
    Row = Row + 1
Next Cels

End Sub

The code does something like below table

	A	B	C
1	1	A	1
2	2	B	A
3	3	C	2
4	4	D	B
5	5	E	3
6			C
7			4
8			D
9			5
10			E

<tbody>
</tbody>

 

[Vanda_a]

Yes... the range you are going to fill has twice as many cells as either column (when starting from Row 1, LastRow is also the number of cells in the column as well as being the row number for the last filled cell).

I see the point. Thank you very much

 

[Vanda_a]

Vanda_a,

  .Range("D1").Resize(UBound(o, 1)).Value = o

It is a personal preference, and, it can be written several ways:

  .Range("C1").Resize(UBound(o, 1)).Value = o
  
  .Range("C1").Resize(UBound(o, 1), UBound(o, 2)).Value = o

I see. Thank you so much

 

[hiker95]

Vanda_a,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

 

[Vanda_a]

Yes... the range you are going to fill has twice as many cells as either column (when starting from Row 1, LastRow is also the number of cells in the column as well as being the row number for the last filled cell).

Hello again.

  Application.Transpose(Split(Join(Evaluate( _
  "TRANSPOSE(A1:A" & LastRow & "&CHAR(1)&B1:B" & _
  LastRow & ")"), Chr(1)), Chr(1)))

Above code. I really cant understand by my own. Please kindly help to give me explanation step by step
Transpose shd be from left to right. Why above one from top to bottom?

 

[Vanda_a]

Split then Join. Split what text and join what text? Then transpose inside transpose. It pulls my hair off

 

[Vanda_a]

Bump