In case you have a requirement on combining multiple columns to on column & you did not have a clue then this whole article is for you. In this article we are going to learn how to combine multiple columns to one column using vba code.
From below snapshot:-
Following is the snapshot of require output:-
We need to follow the below steps:
- Click on Developer tab
- From Code group select Visual Basic
Enter the following code in the standard module
Const shName1 As String = “Sheet1″ ‘Change sheet name here
Const shName2 As String = “Sheet2″
Dim arr, arrNames
arrNames = .Range(“F1″, .Cells(1, Columns.Count).End(xlToLeft))
For i = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
arr = .Cells(i, 1).Resize(, 4)
With .Cells(Rows.Count, 1).End(xlUp)
.Offset(1).Resize(UBound(arrNames, 2), 4) = arr
.Offset(1, 5).Resize(UBound(arrNames, 2)) = Application.Transpose(arrNames)
As you execute the macro; the macro will transfer the data from multiple columns to a single column.
In this way we can combine multiple columns data in a single column.