Merging Column items to row

[nischal4487]

Hi,
I have entries in columns A - x . I want to have all these entries in one cell with a comma(,) after each of the entry in the column cells.
Ex:-
A
B
C
D

Output required in one cell = A,B,C,D

Please help.

Regards,
Nik

 

[Trebor76]

Hi nischal4487,

Try this:

Option Explicit
Sub Macro1()

    Dim lngLastRow As Long
    Dim lngMyRow As Long
    Dim lngMyCol As Long
    Dim strMyText As String
    
    Application.ScreenUpdating = False

    lngLastRow = Range("A:X").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    For lngMyRow = 2 To lngLastRow 'Starts at Row 2. Change to suit.
        strMyText = ""
        For lngMyCol = 1 To 24
            If Len(Cells(lngMyRow, lngMyCol)) > 0 Then
                If Len(strMyText) = 0 Then
                    strMyText = Cells(lngMyRow, lngMyCol)
                Else
                    strMyText = strMyText & "," & Cells(lngMyRow, lngMyCol)
                End If
            End If
        Next lngMyCol
        Cells(lngMyRow, 25).Value = strMyText 'Output to Col. Y. Change to suit.
    Next lngMyRow
    
    Application.ScreenUpdating = True

End Sub

Note, only cells that have data in them will form part of the concatenated string. If you don't want this comment out or delete the If Len(Cells(lngMyRow, lngMyCol)) > 0 Then line and its associated End If line.

Regards,

Robert