How to Combine Multiple Columns into Single Column Using VBA in Microsoft Excel

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

image 1


Following is the snapshot of require output:-

image 2

We need to follow the below steps:

  • Click on Developer tab
  • From Code group select Visual Basic

image 3

Enter the following code in the standard module

Sub MultipleColumns2SingleColumn()

 Const shName1 As String = "Sheet1"       'Change sheet name here

 Const shName2 As String = "Sheet2"

 Dim arr, arrNames

 With Worksheets(shName1)

    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 Worksheets(shName2)

            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)

            End With

        End With


 End With

End Sub


image 4


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.

image 5



Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.