8. VBA Tips - Concatenate Range
This tip is about doing what the Excel CONCATENATE function should be doing, but isn't !
Suppose we want to create a comma-separated list from a range or 2 ranges... How can we do that ?
- Write a customised function in VBA !
Here's out source Data:
Here's the code:
NOTE - the function allows you to enter your own separator, so you can enter comma, space, or even double quotes for nothing...
Here's the output:
Training Video on how to Concatenate a range:
Download sheet to practise how to CONCATENATE a range in Excel
| Attachment | Size |
|---|---|
| concatenate-range.xls | 29.5 KB |
»
- Nick's blog
- Login or register to post comments
- 106257 reads
Great
Really it's great.
8. VBA Tips - Concatenate Range
How would you make it work for non-contiguous ranges?
combine function calls... so
combine function calls...
so in A1:
=ConcatenateRange(myRange,",")
in A2:
=ConcatenateRange(myRange1,",")
in A3, add the 2 ranges above:
=ConcatenateRange(A1:A2,",")
etc..
Not a good idea. It must
Not a good idea. It must happen in one step when 2 or more non contiguous ranges are selected. That will make the function perfect in all senses.
need help
In the above VBA code, you have given First Cell as True and then in last First Cell as False. What does it do in VBA programming. Please advice? I am new to VBA. Thanks in advance.
Regards,
Vishwas K V
To exclude blank cells
Very useful - thanks! For what I needed, I added another IF to exclude blank cells so I don't get repeating commas eg. A,,,D,E becomes A,D,E
Function ConcatenateRange(myRange, Separator)
FirstCell = True
myRangeValues = myRange.Value
For Each thecell In myRangeValues
If FirstCell Then
ConcatenateRange = thecell
Else
If Len(thecell) > 0 Then
ConcatenateRange = ConcatenateRange & Separator & thecell
Else
End If
End If
FirstCell = False
Next
End Function
Help with concatenating hundreds of rows of text.
I have all the code entered and it seems to work correctly at first. However when I enter =ConcatenateRange(A1:A206,",") the information is comma separated like I want, but only up until like 40 or so, not all 206 lines. What am I doing wrong? Your tutorial has been incredibly helpful thus far. Thank you.
PS: I recorded the macro from the code entered in visual basic.
Excel limitation
It's possible if your strings are very long that you have reached an Excel limitation..
copy code
Why it works only when I have open this file. How to copy that to my personal makros. Could you help me?
I need to define myRange,FirstCell, theCell ect. ??
Brgs,
Sylwia
Personal Code
Hi
An overview of the way to do this is to create an XLA (Excel Addin) with this code in it and put that in your Excel Start menu.
The addin then opens when Excel opens, and the function is available to your excel session.
Rename the function to avoid name conflicts if you were to open both the Excel file containing the function AND the XLA.
Let me know if you need more help.
Nick