Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
selecting a range
I just learned about the CONCATENATE function and it seems like what I need
but I often have a good number of cells to pick my data from. Is there a way to select a range of cells, like A1:A5, in lieu of selecting each individual cell A1,A2,A3,A4,A5 ? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
selecting a range
I don't think you can do that...
I use an helper column Enter =A1 in B1 Enter =B1 & ", ", A2 in B2 and copy down... Last cell gives me the concatenation of all cells in Col A, upto that row. "Garth" wrote: I just learned about the CONCATENATE function and it seems like what I need but I often have a good number of cells to pick my data from. Is there a way to select a range of cells, like A1:A5, in lieu of selecting each individual cell A1,A2,A3,A4,A5 ? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
selecting a range
Garth
You could use this UDF to select a range. Results would be comma de-limited. Adjust that to suit. Function ConCatRange(CellBlock As Range) As String 'for a single range =ConCatRange(A1:A10) 'for non-contiguous ranges =ccr((a1:a10,c4,c6,e1:e5)) Dim Cell As Range Dim sbuf As String For Each Cell In CellBlock If Len(Cell.text) 0 Then sbuf = sbuf & Cell.text & "," 'adjust "," to suit like " " for space de-limited Next ConCatRange = Left(sbuf, Len(sbuf) - 1) End Function Also ignores blank cells in the range. Gord Dibben MS Excel MVP On Wed, 4 Mar 2009 10:58:01 -0800, Garth wrote: I just learned about the CONCATENATE function and it seems like what I need but I often have a good number of cells to pick my data from. Is there a way to select a range of cells, like A1:A5, in lieu of selecting each individual cell A1,A2,A3,A4,A5 ? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Please help with selecting a range | Excel Discussion (Misc queries) | |||
Selecting range in list of range names depending on a cell informa | Excel Discussion (Misc queries) | |||
Selecting a Range | Excel Discussion (Misc queries) | |||
Selecting a cell in a range | Excel Worksheet Functions | |||
selecting range with "End + direction" | Excel Discussion (Misc queries) |