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

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