range of cells concatenated to one field?

[cccbzg]

Hello,

I would like to write the value of a range I've selected to a particular cell. In other words, I want to write the value of the entire range to a cell. So for example, the range may include col A rows 3,4, and 5. Can this concatenated range be treated as one value to be output to a cell?
Can't seem to get that to work. Tried Valrange1 = Range("A" & s5row & ":A" & drow).Value, and then Cells(arow, 48) = Valrange1, but that only got me the first value in the range. Thought perhaps I needed DIM Valrange1 AS Range, but that caused an error.



Here's how I set up the range:



With Sheets("INPUT").Range("A" & s5row & ":A" & drow)

Set DXrng = .Find(What:=DDXit, _

After:=.Cells(.Cells.Count), _

LookIn:=xlValues, _

LookAt:=xlPart, _

SearchOrder:=xlByRows, _

SearchDirection:=xlNext, _

MatchCase:=False)

End With



Help much appreciated.

 

[dcardno]

I use a User-Defined Formula for this, as Excel doesn't have a native operator:

Function ConcatAll(rgVals as Range, stSep as String) as String

Dim cl as Range

For Each cl in rgVals
   If cl.Value <> "" Then
      ConcatAll = ConcatAll & cl.Value & stSep
   End If
Next cl

ConcatAll = Left(ConcatAll, Len(ConcatAll) - Len(stSep))

End Function

The stSep just allows me to control what is used as a separator - semi-colons for names on e-mail, commas or spaces for other purposes, etc.