This would be a rather small article. Roughly, what it does is to take all of the cells in given range and to join them one by one, in a way to display them in a shape.
Still with me after this sentence? Great!
E.g. imagine that you have the following example in Excel:
And we want to have it inside a shape. We know, that in order to access the shape text, we simply need to refer it with the “=” sign, so this is not a problem. Then we simply need to know how to create a function, that would give us a result, which can be refered in shape. The ideal outcome looks like this:
So, we obviously need a custom function. It should take the range as an input, and should return something as the top line of the second picture as an output. Once we refer the output to the shape we are ready.
What should we take into account? The fact that we have a paragraph after every second cell. This is quite easy to be achieved with vba. Something else? Probably just to let us being aware what is the difference between “vbCrLf” and “vbCr“, but google can answer this question quite fast for us.
Pretty much that’s all. I will not comment the code, as it is literally less than 15 lines. Here it comes:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
Function RangeUnion(rRange) Dim rCell As Range Dim b As Boolean For Each rCell In rRange b = Not b If b Then RangeUnion = RangeUnion & rCell.Value & vbCr Else RangeUnion = RangeUnion & rCell.Value & vbCrLf End If Next rCell End Function |
Enjoy it!