Concatenate Range and Retain Formatting

In one of my previous posts, I wrote a function to concatenate the values stored in all the cells of a specified range. That function holds good, if you have a text stored in all the cells, and you just want to club them together. It fails if you have cells containing numeric values: dates, percentages, currencies or time. In another post, I wrote a function that returns a formatted string of the value stored in a cell, by automatically fetching the cell’s Number Format.

A more robust Concatenate range function could be obtained by combining both these functions:

'Function to Concatenate the values stored in every Cell of a Range
'and retain Formats
'Author     : Ejaz Ahmed
'Date       : 18 November 2013
'Website    :
Function ConcRangeWithFormats(ByRef WhichRange As Range, _
            Optional ByVal Seperator As String = vbNullString, _
            Optional ByVal ForceVolatile = True) As Variant

'to referesh formats when changed immediately. Would force excel
'to recalculate these cells unconditionally

'Begin by setting the ConcRange to a blank string
ConcRangeWithFormats = vbNullString

'Declare a Range object to use in the For Each Loop
Dim rngCell As Range

'For Each loop concatenates all the cells in the range
For Each rngCell In WhichRange
    If ConcRangeWithFormats = vbNullString Then
        If Not rngCell.Value = vbNullString Then
            ConcRangeWithFormats = AutoText(rngCell, ForceVolatile)
        End If
        If Not rngCell.Value = vbNullString Then
            ConcRangeWithFormats = ConcRangeWithFormats & _
                        Seperator & AutoText(rngCell, ForceVolatile)
        End If
    End If
Next rngCell

End Function

Below is screenshot of a spreadsheet illustrating the use of these Functions:

Concatenate Range with Formats
Concatenate Range with Formats

One thought on “Concatenate Range and Retain Formatting

If you liked it, let me know. If you didn't make sure you let me know!

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s