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

    Next

 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 *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>