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    : http://strugglingtoexcel.wordpress.com/
'===================================================================
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
Application.Volatile

'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
    Else
        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: