Concatenate a range not just individual cells

Hello,

I just began coding using VBA and I am way too much into it right now. This is my first post so I hope I'm in the right place.
I have a table that looks something like this (just much, much larger):

Table2.PNG

I need each cell in the last column (Unique Part ID) to combine values of all the cells on the same row except for the cells in the first column (ID).
In other words, it is like function CONCATENATE (). However, I do not want the code to use actual references (as it would if I were to record a macro of myself entering the function). Also, the number of Part columns can change, so I need this code to be dynamic. The only thing we know for sure is that all Part columns will be between the ID column and Unique Part ID column.

The end result would look like the image below:

Table1.PNG

I hope you guys are able to assist me. Excel 2016 has a function called CONCAT. With this function, you are able to select a range of cells e.g.

Formula:

=CONCAT(TABLE1[@[Part1]:[Part4]])

and it would use each cell in the range as an individual entity while joining strings together. However, this code I'm writing is for MS Excel 2013

P.S. If you are able to assist by writing a function, would you be able to also include instructions on how the module I am working on can call on that function? I know how to call out each Sub (), but for some reason having issues calling out my functions that I create code for.

Thank you guys very much!!

Hello welcome to the forum.

This code works for me with Table 1 as a mock-up of your image.

Sub Concatenate_Loop()

Dim finalValue As String
Dim c As Range, cell As Range, Rng As Range

    For Each c In Range("Table1[Unique Part ID]")
    finalValue = ""
    Set Rng = Range(Cells(c.Row, 2), Cells(c.Row, c.Column - 1))

    For Each cell In Rng
    
        finalValue = finalValue + IIf(CStr(cell.Value) <> "", CStr(cell.Value) & " ", "")
        
    Next cell
        c.Value = finalValue
    Next c
    
End Sub

This runs down the Table's 'Unique Part ID' and concatenates the numbers in each row if the cell is not empty.

Is this what you're looking to do?

DBY

Thank you very much DBY! I will test it once I'm in front of my PC later today and will let you know the outcome.

I don't mean to be a drag, but I am still in learning stages of VBA.
By any chance, can you comment on each line of the code to explain what it is that they are doing? No rush on this, I am just trying to understand how and why things work...hoping it would eliminate the need to ask if similar problem arrises.

Thanks again for your help DBY!

Hi
I need to leave my PC for a while now but I'll add some explanation later.