8. VBA Tips - Concatenate Range

Nick's picture


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:

concatenate-range

Here's the code:

concatenate-range

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:

concatenate-range

Training Video on how to Concatenate a range:

Download sheet to practise how to CONCATENATE a range in Excel

AttachmentSize
concatenate-range.xls29.5 KB

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.

Nick's picture

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

Nick's picture

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

VBA Adds ?

Hi,

Thanks for fast answer. Yes true I can do xlam (I've 2007 Office)- I didn't do it before just I have macro file durning sessions. I don't know what I'm doing wrong? This .xlam is opening durning excel session but function dosen't work. (but it is working in .xlsm module ) - so I can't using xlam adds? what to do?

Regards from Poland
Sylwia

Nick's picture

Excel Addins

ok, here are the steps.

1. Download the Concatenate-Range.xls spreadsheet from this post.

2. File => saveAs.. and Choose Excel Addin.

.. for me, that saves here:

C:\Documents and Settings\Any Authorised User\Application Data\Microsoft\AddIns\Concatenate-Range.xlam

3. Go to Excel options, choose Addins, manage, GO, and activate Concatenate-Range

Nick