VBA Express : Excel - Function to Concatenate a Range, with Optional Delimiter, Value Type and Skip Blanks

Excel

Function to Concatenate a Range, with Optional Delimiter, Value Type and Skip Blanks

Ease of Use

Easy

Version tested with

2000, 2002

Submitted by:

matthewspatrick

Description:

Improves on Excel's native CONCATENATE() function by allowing the user to specify a range of cells to be concatenated, what delimiter to use if any, whether to concatenate cell values or their displayed values, and whether to skip blank cells in the range.

Discussion:

Excel's native functionality for concatenation (either by using the CONCATENATE() function, or using the & operator) requires specifying each individual item as well as the delimeter as pieces of the statement, and has limitations. The MCONCAT function also available has limitations as the delimeter if desired would have to be included in the range. This function allows for the optional delimeter within the desired range to be concatenated. This function allows you to simplify the process, and add new options. ..likconcatenatingdisplayed values (handy if you have to concatenate currency or floating-point numbers) or real values, and whether or not to skip blank cells. Thanks to VBAX members mvidas and rberke, who made substantial contributions to this function's development.

Code:

instructions for use

			Option Explicit 
 
Function ConcRange(Substrings As Range, Optional Delim As String = "", _ 
    Optional AsDisplayed As Boolean = False, Optional SkipBlanks As Boolean = False) 
     
     ' Concatenates a range of cells, using an optional delimiter.  The concatenated
     ' strings may be either actual values (AsDisplayed=False) or displayed values.
     ' If NoBlanks=True, blanks cells or cells that evaluate to a zero-length string
     ' are skipped in the concatenation
     
     ' Substrings: the range of cells whose values/text you want to concatenate.  May be
     ' from a row, a column, or a "rectangular" range (1+ rows, 1+ columns)
     
     ' Delimiter: the optional separator you want inserted between each item to be
     ' concatenated.  By default, the function will use a zero-length string as the
     ' delimiter (which is what Excel's CONCATENATE function does), but you can specify
     ' your own character(s).  (The Delimiter can be more than one character)
     
     ' AsDisplayed: for numeric values (includes currency but not dates), this controls
     ' whether the real value of the cell is used for concatenation, or the formatted
     ' displayed value.  Note for how dates are handled: if AsDisplayed is FALSE or omitted,
     ' dates will show up using whatever format you have selected in your regional settings
     ' for displaying dates.  If AsDisplayed=TRUE, dates will use the formatted displayed
     ' value
     
     ' SkipBlanks: Indicates whether the function should ignore blank cells (or cells with
     ' nothing but spaces) in the Substrings range when it performs the concatenation.
     ' If NoBlanks=FALSE or is omitted, the function includes blank cells in the
     ' concatenation.  In the examples above, where NoBlanks=False, you will see "extra"
     ' delimiters in cases where the Substrings range has blank cells (or cells with only
     ' spaces)
     
    Dim CLL As Range 
     
    For Each CLL In Substrings.Cells 
        If Not (SkipBlanks And Trim(CLL) = "") Then 
            ConcRange = ConcRange & Delim & IIf(AsDisplayed, Trim(CLL.Text), Trim(CLL.Value)) 
        End If 
    Next CLL 
     
    ConcRange = Mid$(ConcRange, Len(Delim) + 1) 
     
End Function

How to use:

Enter the VB Editor by selecting Tools|Macro|Visual Basic Editor from the menu
In the VB Editor, insert a new regular module into your workbook's VB Project
Paste the above code into that module
Use the function in your other VBA routines (i.e., SomeVariable = ConcRange(ActiveSheet.[a1:a10], ", "), or in worksheet formulas (i.e., =ConcRange(A1:A10,", "))

Test the code:

Try out the function using varying ranges and optional argument settings.
See the example file for a few samples of how to use this function.

Sample File:

ConcRangeExamples.zip 10.82KB

Approved by mdmackillop

This entry has been viewed 383 times.