Collect column range value combined in one cell followed by comma and end with & seperator

Question

I am trying to get the collect combined value in one cell followed by comma and end with &
Column range : AM4:AM20 may be with value or blank.
I need to collect this value combined together with comma and with & seperator

AM4 = ABCD1

AM5 = ABCD2

AM6 = ABCD3

AM7 = ABCD4

AM8 = "" AM9 =""

eg., BB4 = ABCD1, ABCD2, ABCD3 & ABCD4

I tried this code

Sub GetPipeData()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim cell As Range
    Dim collectedValues As String
    Dim addComma As Boolean

    ' Set the worksheet containing the values
    Set ws = ThisWorkbook.Sheets("INPUT") 

    ' Find the last used row in column AM
    lastRow = ws.Cells(ws.Rows.Count, "AM").End(xlUp).Row

    ' Initialize the collectedValues variable
    collectedValues = ""
    addComma = False ' Initialize the flag to not add a comma

    ' Loop through the cells in column AM from row 4 to the last used row
    For Each cell In ws.Range("AM4:AM20" & lastRow)
        ' Check if the cell is not empty
        If Not IsEmpty(cell.Value) Then
            ' If addComma is True, add a comma before the value
            If addComma Then
                collectedValues = collectedValues & ", "
            End If
            ' Add the value to the collectedValues variable
            collectedValues = collectedValues & cell.Value
            ' Set addComma to True for the next iteration
            addComma = True
        End If
    Next cell

    ' Print the collected values in cell BB4
    ws.Range("BB4").Value = collectedValues
End Sub

Expected result

**BB4 = ABCD1, ABCD2, ABCD3 & ABCD
**
Please help me to achieve this task. Thank you :)

Answer 1

Or you could just use a worksheet formula:

=LET(
    Φ,AM4:AM20,
    SUBSTITUTE(TEXTJOIN(", ",,Φ),", "," & ",MAX(1,SUM(N(Φ<>""))-1))
)