Sub mergeColumn()
Dim lr As Long, lr2 As Long
lr = Range("A" & Rows.Count).End(3).Row
lr2 = Range("B" & Rows.Count).End(3).Row
Range("C1").Resize(lr).Value = Range("A1:A" & lr).Value
Range("C" & lr + 1).Resize(lr2).Value = Range("B1:B" & lr2).Value
End Sub
let
Source = Table.ToColumns(Excel.CurrentWorkbook(){[Name="Table1"]}[Content]),
TFL = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
Exp = Table.ExpandListColumn(TFL, "Column1")
in
Exp
Column1 | Column2 | Column1 | |
1 | A | 1 | |
2 | B | 2 | |
3 | C | 3 | |
A | |||
B | |||
C | |||
Try this:
VBA Code:Sub mergeColumn() Dim lr As Long, lr2 As Long lr = Range("A" & Rows.Count).End(3).Row lr2 = Range("B" & Rows.Count).End(3).Row Range("C1").Resize(lr).Value = Range("A1:A" & lr).Value Range("C" & lr + 1).Resize(lr2).Value = Range("B1:B" & lr2).Value End Sub
Sub Merge_Columns()
Dim j As Range, r As Range
Dim lr1 As Long, lr2 As Long
lr2 = 1
Set r = Range("A:C") 'columns to merge
For Each j In r.Columns
lr1 = Cells(Rows.Count, j.Column).End(3).Row
r.Cells(lr2, r.Columns.Count + 1).Resize(lr1).Value = j.Range(Cells(1, 1), Cells(lr1, 1)).Value
lr2 = r.Cells(Rows.Count, r.Columns.Count + 1).End(3).Row + 1
Next
End Sub