Today I'm going to explain you how to concatenate values in VBA. Excel application already have an inbuilt function to concatenate values in cells. What it does is, it just concatenate values in each cell. It is a very helpfull inbuilt function. But we often need more custom concatenations than that. So I will explain you how you can use VBA to concatenate values in cells according to your requirements.
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.
Define the sheet variable.
|
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 = "" |
