Hi All,
I have been using the below function for concatenating the column values for about 2 years now and recently i upgraded to Office 2007, where it has stopped working. Earlier i was using Office 2003. I got this function from an aquantance of mine back then and i'm not sure why it is not working in Office 2007. Any help would be appreciated as it is hampering my daily work.
I have tried:
1. Running the excel sheet in compatibility mode
2. Saving as .xlsm
but in vain...
Below is the Function:
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
I have been using the below function for concatenating the column values for about 2 years now and recently i upgraded to Office 2007, where it has stopped working. Earlier i was using Office 2003. I got this function from an aquantance of mine back then and i'm not sure why it is not working in Office 2007. Any help would be appreciated as it is hampering my daily work.
I have tried:
1. Running the excel sheet in compatibility mode
2. Saving as .xlsm
but in vain...
Below is the Function:
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
