11 thoughts on “JoinRange”

1. I’m sure I read somewhere that it’s a legal requirement that everyone have their own VBA cell-join function.

   Mine lacks the start and end thing, but includes a flag to include SQL-safety, like putting strings in quotes and dates into something the database wouldn’t choke on.

   I also have a one line CellSplit function, but I don’t think there’s any law about that one.

2. Function together(c0 As Range)
     together = Join(WorksheetFunction.Transpose(WorksheetFunction.Transpose(c0)), “||”)
   End Function

3. Hi Dick –

   This is a coding style question, and since I’ve done it both ways, I’m obviously not smart enough to have a preference.

   sReturn is superfluous to JoinRange. Do you consider your the code cleaner and more self-documenting using it, at the (negligible) expense of a second string variable?

   Or are you like me, and equivocate, based on the day of week * phase of the moon?

   …mrt

4. Using rCell.Text gives you the displayed formatted cell – seems a bit dodgy to me (could be #### or currency or anything the user has decided to format the cell as).

   Why not use the unformatted contents of the cell?

5. Michael: I have no good reason for doing it that other than “I like it”. I just don’t like using the function name within the function.

   Charles: I needed to move an Excel sheet to a wiki and that prompted me to write this. In the wiki, everything is text. So if something was formatted with two decimal place and no comma, then that’s what I wanted to get transferred over. If I use Value or Value2, I lose that.

6. Well Mike, I think you’re right.

   Here’s mine for completeness.
   It will aggregate a while range, not necessarily just a row, and it uses .value, but also .NumberFormat to address the issue of formatted cells. (I don’t think I knew about .text)

   ‘Concatenates the selected range of cells
   ‘ Right to left, then top to bottom
   ‘ Optional delimiter string
   ‘ Interprets numbers (& dates and times) in their visible number format (i.e. WYSIWYG)
   Function concatenateRange(r As Range, Optional delimiter As String = “”)
   Dim result As String
   Dim x As Range
   result = “”

   For Each x In r.Cells
   result = result & delimiter & IIf(x.value = “”, “”, Application.WorksheetFunction.Text(x.value, x.NumberFormat))
   Next x

   concatenateRange = Mid(result, Len(delimiter) + 1)
   End Function

   Rick

7. Hello,

   I think that the subject leads to very imaginative codings
   I’ve for my part built a function that concatenate 1024 characters-long strings with/without a separator that I can use in a spreadsheet and a “index” parameter let me access the output substrings (I mean if the concatenation goes over 1024).

   here’s a simplier code, to show differents codings as above.

   Function range_Concatenate(ByRef Plage As Range, Optional Separateur$) As String
   ‘first posted on the web by LL
   Application.Volatile: Dim i&:

   If Plage.count = 1 Then range_Concatenate = Plage(1): Exit Function

   range_Concatenate = Plage(1)
   For i = 2 To Plage.count
       If Len(Plage(i))  0 Then range_Concatenate = range_Concatenate & Separateur & Plage(i)
   Next i
   End Function

8. Here’s a function that I built and that used to go along with the previous one

   Public Function UDF_PutInClipboard(Optional HandledData As Variant)
   Dim dobPressepapiers As DataObject
   Set dobPressepapiers = New DataObject
   If IsEmpty(HandledData) Then Exit Function
   If IsObject(HandledData) Then If HandledData Is Nothing Then UDF_PutInClipboard = CVErr(xlErrNA): Exit Function
   If TypeName(HandledData) = “String” Then dobPressepapiers.SetText HandledData _
       Else UDF_PutInClipboard = CVErr(xlErrNA): Exit Function
   dobPressepapiers.PutInClipboard
   End Function

9. […] cruel, but a sentiment otherwise understandable. Now only the other day, Dick Kusleika posted his take on the everybody-has-one timeless RangeJoin() UDF topic. Here’s a rather simpler […]

10. Pingback: Daily Dose of Excel » Blog Archive » JoinRange Update

11. I recently wrote an updated function today for a specific program input. It concatenates cells, but will compact ranges of values that are continuous.
    e.g. Cells 1,2,3,4,5,8,9,10,15 will return a result “1-5,8-10,15”.
    Here is the code. You can change the defaults of the optional parameters to suit your needs:
    
Function compactRange(r As Range, _
Optional endWithDelimiter As Boolean = True, _
Optional delimiter As String = ",", _
Optional rangeDelimiter As String = "-")
Dim result As String
Dim x As Range
Dim inRange As Boolean
Dim lastX As Long

    result = ""
    lastX = -999 ' just a number we won't accidentally have in our data
    inRange = False

    For Each x In r.Cells
    If x = lastX + 1 Then
    If inRange Then
    'add nothing
    Else
    'start a range
    result = result & rangeDelimiter
    inRange = True
    End If
    Else
    If inRange Then
    'complete the range
    result = result & lastX
    inRange = False
    End If
    result = result & delimiter & IIf(x.Value = "", "", Application.WorksheetFunction.Text(x.Value, x.NumberFormat))
    End If
    lastX = x
    Next x
    If endWithDelimiter Then result = result & delimiter

    compactRange = Mid(result, Len(delimiter) + 1) 'cuts the delimiter off the start of the string
    End Function

    Enjoy.
Rick

Posting code? Use <pre> tags for VBA and <code> tags for inline.