Concatenate a range not just individual cells

Hi
Below is my code with some remarks included:

'Declare Variables
Dim finalValue As String
Dim c As Range, cell As Range, Rng As Range

'Start loop of 'Unique ID' field.
    For Each c In Range("Table1[Unique Part ID]")
    'Set variable 'finalValue to empty.
    finalValue = ""
    'Set the range to be concatenated.
    Set Rng = Range(Cells(c.Row, 2), Cells(c.Row, c.Column - 1))

    'Start loop of each cell in above range.
    For Each cell In Rng
        'Concatente each non empty cell into the 'finalValue' string variable.
        finalValue = finalValue + IIf(CStr(cell.Value) <> "", CStr(cell.Value) & " ", "")
        
    Next cell
        'Place 'finalValue' in 'Unique ID' field.
        c.Value = Trim(finalValue)
'Go to next cell in 'Unique ID' field.
    Next c

Mike in post #5 has given you a more succinct piece of code as another alternative. You could also turn my example into a UDF (User Defined Function) and add this to the 'Unique ID' column as a formula:

Function ConcatenateCells(sourceRange As Excel.Range) As String
    Dim finalValue As String

    Dim cell As Excel.Range

    For Each cell In sourceRange.Cells
        finalValue = finalValue + IIf(CStr(cell.Value) <> "", CStr(cell.Value) & " ", "")
    Next cell

    ConcatenateCells = Trim(finalValue)
End Function

Spoiled for choice but you did ask!