CODEWITHSUNDEEP
vbaexcel
alphaService
alphaService

Reputation: 131

Concatenation of the values of 3 columns row by row

I am trying to concatenate the values of columns (1, 2, 3). The result should be in column (4). There are n-rows for which the concatenation should be made. With the code I made, each row is worked on but the result in each result cell is added and added. What did I do wrong?

Sub insertStatement()
Dim row As Integer
Dim lrow As Integer
Dim x As String
Dim cel As Range
Dim rng As Range

Sheets("INSERT").Select
row = 1
lrow = Cells(Rows.Count, 1).End(xlUp).row

Do While Cells(row, "A").Value <> ""
    With Worksheets("INSERT")
        Set rng = Range(.Cells(1, 1), .Cells(lrow, 3))
    End With

    x = ""

    For Each cel In rng

        x = x & cel.Value

    Next

    Sheets("Insert").Cells(row, 4).Value = x

row = row + 1
Loop

End Sub

Upvotes: 0

Views: 1342

Answers (2)

PatricK
PatricK

Reputation: 6433

Try below for this simple goal.

Sub insertStatement()
    Const sFormulaR1C1 = "=CONCATENATE(RC[-3],RC[-2],RC[-1])"
    Dim lLastRow As Long

    With ThisWorkbook.Worksheets("INSERT")
        lLastRow = .Cells(Rows.Count, "A").End(xlUp).row
        .Range("D1:D" & lLastRow).FormulaR1C1 = sFormulaR1C1
    End With
End Sub

Upvotes: 3

bmshort
bmshort

Reputation: 216

At the line where you are setting your range...

Set rng = Range(.Cells(1, 1), .Cells(lrow, 3))

...you are using row 1, column 1 as your start point every time. Would suggest the following...

Set rng = Range(.Cells(row, 1), .Cells(lrow, 3))

Upvotes: 0

Related Questions