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
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.
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.
together = Join(WorksheetFunction.Transpose(WorksheetFunction.Transpose(c0)), "||")
End Function
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
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?
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.
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)
' 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
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.
'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
Here’s a function that I built and that used to go along with the previous one
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
[...] 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 [...]