Concatenating rows into a single cell

[Deniouz]

Is there a way to dynamically concatenate rows into a single cell using VBA code?

I have a sample where the column highlighted in yellow would be the desired outcome.

[davesexcel]

You can loop through the range areas.

Sub Button1_Click()
    Dim sh As Worksheet, LastRw As Long
    Dim RangeArea As Range, rng As Range, c As Range
    Dim s As String, x As String
    Set sh = ActiveSheet
 
    With sh
 
        LastRw = .Cells(.Rows.Count, "B").End(xlUp).Row
 
        Set rng = .Range("B2:B" & LastRw)
 
        For Each RangeArea In rng.SpecialCells(xlCellTypeConstants, 23).Areas
            For Each c In RangeArea
                s = s & c
            Next c
 
            RangeArea(1).Offset(, 1) = s
            s = ""
        Next RangeArea
 
    End With
End Sub

[Phuocam]

Or try:

Public Sub MyConcat()
Dim lastRow As Long
Dim i As Long
Dim temp As String
lastRow = Range("B" & Rows.Count).End(xlUp).Row
For i = lastRow To 2 Step -1
    temp = Range("B" & i).Value & temp
    If Range("A" & i).Value <> "" Then
        Range("C" & i).Value = temp
        temp = ""
    End If
Next i
End Sub 

[mohadin]

Hi,
What about

Sub Deniouz()
    Dim lr, ar
    lr = Cells(Rows.Count, "B").End(xlUp).Row
    For Each ar In Cells(2, 2).Resize(lr).SpecialCells(xlCellTypeConstants, 23).Areas
        ar.Offset(, 1).Resize(1) = Join(Application.Transpose(ar), "")
    Next
End Sub