This is an example worksheet.

Assume we need all the words in column A to E of each row to show up in column F with a comma and space between them.

Here is the VBA code which can accomplish that.

Sub ConcatenationExample() Dim WS As Worksheet Set WS = ActiveSheet 'find last row Lastrow = WS.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row Dim result As String For i = 1 To Lastrow For j = 1 To 5 If WS.Cells(i, j) <> "" Then If result <> "" Then result = result & ", " & WS.Cells(i, j) Else result = result & WS.Cells(i, j) End If End If Next j WS.Cells(i, 23) = result result = "" Next i End Sub |

You will get following result after running above macro

Next I will explain you the code step by step.

Dim WS As Worksheet |

assign active sheet to worksheet variable

Set WS = ActiveSheet |

Find the last row of the sheet

'find last row Lastrow = WS.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row |

Define a variable to hold result in each loop

Dim result As String |

Loop through rows

For i = 1 To Lastrow Next i |

Loop through columns from A to E

For j = 1 To 5 Next j |

Check whether cell is not empty

If WS.Cells(i, j) <> "" Then End If |

We don't need comma at beginning. So we check result string to identify whether we going to concatenate first value or not.

If result <> "" Then result = result & ", " & WS.Cells(i, j) Else result = result & WS.Cells(i, j) End If |

Write result to column F of each row

WS.Cells(i, 6) = result |

Clear the result variable for next loop

result = "" |