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 : https://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: