concatenate a range of data

[jt]

is there a way to concatenate a range instead of having to
individually select each cell, thanks in advance

 

[James Ravenswood]

Hi-light the range and run Builder, then select an empty cell and run Putter. Here are the macros:

Dim sf As String

Sub builder()
sf = ""
For Each r In Selection
If sf = "" Then
sf = "=" & r.Address
Else
sf = sf & "&" & r.Address
End If
Next
sf = Replace(sf, "$", "")
MsgBox sf
End Sub

Sub putter()
ActiveCell.Formula = sf
End Sub

 

[Gord Dibben]

Function ConCatRange(CellBlock As Range) As String
Dim cell As Range
Dim sbuf As String
For Each cell In CellBlock
If Len(cell.text) > 0 Then sbuf = sbuf & cell.text & ", "
Next
ConCatRange = Left(sbuf, Len(sbuf) - 2)
End Function

usage is: =ConCatRange(A1:A10)

This gives a comma/space delimited list in one cell.

You can adjust that to suit by changing or removing & ", "


Gord

 

[Jim Cone]

With xl2000 or later versions, you can use the VBA.Join function.
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(Data Options add-in: rows/dates/random stuff)





"jt" <[email protected]>
wrote in message
news:[email protected]...

 

[Ron Rosenfeld]

is there a way to concatenate a range instead of having to
individually select each cell, thanks in advance

Here is a User Defined Function you can use:

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=ConcatRange(range, [delimiter])

in some cell.

The delimiter is optional and, if omitted, will default to a <space>


==========================
Option Explicit
Function ConcatRange(rg As Range, Optional sDelim As String = " ") As String
Dim v1 As Variant, v2() As Variant, v As Variant
Dim i As Long
v1 = rg
ReDim v2(0 To rg.Count - 1)
i = 0
For Each v In v1
v2(i) = v
i = i + 1
Next v
ConcatRange = Join(v2, sDelim)
End Function
===============================