JoinRange

Here’s a UDF that’s been done about a million times before. So why write my own? Oh, I don’t know. It’s faster to write it than to find one on the internet and modify it I suppose.

Public Function JoinRange(rInput As Range, _
Optional sDelim As String = "", _
Optional sLineStart As String = "", _
Optional sLineEnd As String = "", _
Optional sBlank As String = "") As String

Dim sReturn As String
Dim rCell As Range

sReturn = sLineStart

For Each rCell In rInput.Cells
If IsEmpty(rCell.Value) Then
sReturn = sReturn & sBlank & sDelim
Else
sReturn = sReturn & rCell.Text & sDelim
End If
Next rCell

sReturn = Left$(sReturn, Len(sReturn) – Len(sDelim))

sReturn = sReturn & sLineEnd

JoinRange = sReturn

End Function

Example 1: Create a wiki table.

Example 2: Create an html table

Company Name City Zip
Rouster and Sideways Coral Hills 21155
Omni Consimer Products West View 47135
Smith and Co. nbsp; 24737
Sonky Rubber Goods Hollins 99681
Smith and Co. Geneseo 48472

Whoops, looks like I forgot the ampersand in front of my nbsp. Oh well.

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. 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

Leave a Reply

Your email address will not be published. Required fields are marked *