Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
trying to combine cells with shortest equation
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I use a cells from one worksheet in the equation of another | Excel Discussion (Misc queries) | |||
Combine cells with the same reference and combine quantities | Excel Discussion (Misc queries) | |||
Have problem in Manipulating Workbook Object and make macro shortest as possible | Excel Worksheet Functions | |||
=IF(AND) equation pulls through N/A cells | Excel Discussion (Misc queries) | |||
How to obtain the shortest range that encloses all the non empty cells? | Excel Programming |