Concat Excel Column Cell Strings per row with comm

Question

I have an excel file with some columns. I have been trying to merge the columns and keep the cell strings separated with a comma.

So, I want a new table with the concat results per row :

=CONCATENATE(A2,",",B2,",",C2,",",D2)
=CONCATENATE(A3,",",B3,",",C3,",",D3)
=CONCATENATE(A4,",",B4,",",C4,",",D4)

I tried to use VBA but with no success :

Sub sisk()
Dim sisk As String

For i = 2 To 4
    sisk = CONCATENATE(Range(Cells(1, i).Value), Cells(1, 4).Value)
Next i
Worksheets("Sheet1").Cells(1, 6) = sisk
End Sub

Answer 1

Most of the worksheet functions can be used in VBA like that:

Application.WorksheetFunction.Sum(1, 2)

However, some worksheet functions cannot be used in VBA, and unfortunately CONCATENATE is one of them.

& is operator used in VBA to concatenate strings.

---

The code below should work for you:

Sub sisk()
    Dim sisk As String
    Dim row As Long
    Dim col As Long

    For row = 2 To 4
        sisk = vbNullString
        For col = 1 To 4
            If VBA.Len(sisk) Then sisk = sisk & ","
            sisk = sisk & Cells(row, col)
        Next col

        Worksheets("Sheet1").Cells(row, 6) = sisk

    Next row


End Sub