Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

Concatenate using VBA

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 = ""

Contact Form

Name

Email *

Message *

Popular Posts