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

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

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


On Sat, 17 Mar 2012 06:04:15 -0700 (PDT), jt
wrote:

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

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"
wrote in message
...
is there a way to concatenate a range instead of having to
individually select each cell, thanks in advance

On Sat, 17 Mar 2012 06:04:15 -0700 (PDT), jt wrote:

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

Hello! I'm not sure if I understand the question, but I think to concatenate a range of data, You just use = A1&B2 and just drag it all over the range you want to concatenate.

I also learned about concatenate function through https://www.efinancialmodels.com/kno...se-2-formulas/. You can check this out.

Hope this helps!