ConcatRange

[iod1982]

Hey everyone,

I am trying to write a VBA function that will allow me to pass it a single range argument and that returns the concatenation of all the cells in the range.

Please help

 

[Bong]

If "r" is your range, how about

total = ""
for each c in r.cells
total = total & c.value
next


_________________
Bob Rashkin

 

[rmikesmith]

Very similar to Bong's solution, here is a complete function:

Function ConcatCells(ByVal oRange As Range) As String
Dim oCell As Range
Dim sResult As String

   sResult = ""
   For Each oCell In oRange
     sResult = sResult & oCell.Text
   Next oCell
   ConcatCells = sResult
   
End Function

Regards,
Mike

 

[SkipVought]

Hi,

The RANGE is already a concatenation of all the cells in the range.

However, if you want the concatenation of all the VALUES in the range...

Function ConcatValues(rng as range) as string
   Dim r as range
   for each r in rng
      ConcatValues = ConcatValues & r.value
   next
End Function

Skip,

_{[red]Be Advised![/red] A man's home is his castle...
in a Manor of speaking.}