Hi Excellers, and welcome back to another #MacroMonday #Excel blog post in my 2020 Excel and Macro series. The macro I am going to show you today is one I use on a regular basis. It will concatenate a range of cells (account references) and builds a large one-line row string. Each of the account references is separated by a comma. I use this to query a database with a bunch of customer references as a single strong, at once instead of one by one. Time and sanity saver right there!.
You can see an example of the type of data I am using below in the screenshot.
After the macro runs, the result is a cool 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.
Starting The Macro.
First, you will need to open the Visual Basic Editor. There are two 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?. Well, if you save the macro in your Personal Macro workbook it will be available in any Excel workbooks. 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 I will store it in my Personal Macro Workbook. When you select this option then Excel creates (if it is not already created) this 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.
Preparing To Write The Macro.
We need to start off the process by inserting a New Module. Do this by selecting the Personal.xlsbworkbook, then Insert Module. 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 simply need to enter the rest of the code between these two lines.
We need to declare two variables for this macro. This ensures that Excel creates memory containers for these values. In this example, we need to declare the following.
This ensures the user-selected range is stored in the Excel memory, and i is set as the data type string. By declaring these variables Excel will save processing time and memory.
Using For Each In Excel Range.
One of the most common things you will do when programming VBA in Excel is looping through a collection of cells in a specified range. In this instance, we are using the For Each to loop through all of the cells in the range and concatenate each one of them using the & as well as “,”. The looping continues until all of the cells have been concatenated.
Ending The Macro.
The code ends once all looping of cells has been completed with the “End Sub" piece of code. This was already entered into the module for us when started the type the name of the macro.
If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
Likewise, if you want to see all of the blog posts in the Macro Mondays Series Click The Link Below
So, Don’t forget to SUBSCRIBE to the How To Excel At Excel Newsletter for more tips the first Wednesday of the month.