Hi Excellers, and welcome back to another #MacroMonday #Excel blog post in my 2020 Excel and Macro series. This Excel VBA macro walks you through how to use VBA to concatenate a range of cells. This macro will build a large one row string. In this example data set, a list of customer account references are joined, each one separated by a comma.
After the macro runs, the result is a long string which I can query my database with like below. How cool is that?
Let’s work through the coding together!.
What Does The Macro Do?
This procedure will concatenate a range of cells with a comma separator using VBA. Yes, VBA concatenate.
Starting The Macro.
First, open the Visual Basic Editor. There are tow ways to do this.
- Either by hitting ALT +F11 or
- selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result.
Before we begin to write any code you need to decide where to store the code. You have a choice.
- To store your code either in your Personal Macro Workbook or
- Store it in your current workbook.
What’s the difference?. If you save the macro in your Personal Macro workbook, it will be available in any Excel workbooks. However, if you store it in the current workbook, then use is restricted to that workbook. In this instance, I may want to reuse the code, so in this instance, I store it in my Personal Macro Workbook. When you select this option, Excel creates (if it is not already created) the Personal Macro Workbook and saves the macro in that location. By default, this macro workbook is named Personal.xlsb.
If you want more details on creating and updating your personal macro workbook then I recommend my blog posts below.
Creating and Updating Your Personal Macro Workbook
Why Is My Personal Macro Workbook Not Loading Automatically?
Create A Shortcut To Your Personal Excel Macro Workbook
Preparing To Write The Macro.
We need to start the process by inserting a new module. Do this by selecting the Personal.xlsb workbook, then Insert Module. Next, type Sub, then the name of your macro. In this example, I have called the macro Combine. Notice that Excel will automatically enter the end text End Sub to end the Sub Routine. We need to enter the rest of the code between these two lines.
Sub Combine()
End Sub
Declaring Variables.
We need to declare two variables for this macro. By doing this, Excel creates memory containers for these values.
Dim rng As Range
Dim i As String
The user-selected range and the string data type are set. By declaring these variables, Excel will save processing time and memory.
Using the For Each Looping Process.
Looping through a collection of cells in a specified range is possibly one of the most common VBA methods. In this macro, I use the For Each process to loop through all cells in the range. First, Excel joins the cell contents with the “&” as well as “,”. Once Excel joins all the cells, the looping ends.
For Each rng In Selection
i = i & rng & ","
Next rng
Ending TheVBA Concatenate Macro.
The macro ends once all looping of cells is complete. The ending is the End Sub code. This last piece of code already exists from the first stage of naming the VBA Maco.
End Sub
That is how to use VBA to concatenate cell contents.
Copy The Code Here.
Sub Combine()
'macro by How To Excel At Excel
Dim rng As Range
Dim i As String
For Each rng In Selection
i = i & rng & ","
Next rng
End Sub
Finally, do you want even more Excel and VBA tips?.
Sign to my Excel Newlstter. I share three FREE Excel tips every month. No spam.
Alternatively bookmark my Macro Monday and Formula Friday pages. I update them every week.
