concatenate a range of data

  • Thread starter Thread starter jt
  • Start date Start date
J

jt

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
 
Back
Top