ExcelBanter - trying to combine cells with shortest equation

MtK

User defined Function.

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) - 1)
End Function

Usage is: =ConCatRange(C1:C5)

Macro.........

Sub ConCat_Cells()
Dim x As Range
Dim y As Range
Dim z As Range
Dim w As String
Dim sbuf As String
On Error GoTo endit
w = InputBox("Enter the Type of De-limiter Desired")
Set z = Application.InputBox("Select Destination Cell", _
"Destination Cell", , , , , , 8)
Application.SendKeys "+{F8}"
Set x = Application.InputBox("Select Cells...Contiguous or
Non-Contiguous", _
"Cells Selection", , , , , , 8)
For Each y In x
If Len(y.text) 0 Then sbuf = sbuf & y.text & w
Next
z = Left(sbuf, Len(sbuf) - 1)
'Application.SendKeys "+{F8}"
Exit Sub
endit:
MsgBox "Nothing Selected. Please try again."
End Sub

Watch for word-wrap. Note continuation marks "_"

Gord Dibben Excel MVP

On 23 Jun 2004 09:46:29 -0700, (MtK) wrote:

So I have this equation that looks like

=C1&" "&C2&" "&C3&" "&C4&" "&C5

I was thinking maybe I could reduce the equation to just show the
first and last cell like =C1:C2 but that doesn't work and even if it
did i need the space between the two cells. and help are creating a
reduced equation that uses the first and last cells and has a space
would be greatly appreciated. thanks.