Hi Gord,
Tried it but no joy I'm afraid.
I have 4 columns with 139 rows, each cell has approx 20 characters in
it. I want to copy groups of cells in a column (anything up to approx
11 cells) into a single cell on a separate .xls spreadsheet (not a
sheet in the same workbook).
Dave.
Gord Dibben Wrote:
How many cells are you talking about and how much text is in them?
What you want can be done but may not be practical due to some limits
in Excel.
Maybe some other method could be employed?
If not, use this UDF to stick the text into one cell....comma delimited
as
written and ignores blank cells in the range.
If don't want a comma just delete that from the code.
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 & ","
'remove & "," if you don't need comma de-limited
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function
On Sheet2 enter =ConCatRange(Sheet1!A1:A10)
If not familiar with VBA and macros, see David McRitchie's site for
more on
"getting started".
http://www.mvps.org/dmcritchie/excel/getstarted.htm
In the meantime..........
To create a General Module, hit ALT + F11 to open the Visual Basic
Editor.
Hit CRTL + R to open Project Explorer.
Find your workbook/project and select it.
Right-click and InsertModule. Paste the above code in there. Save
the
workbook and hit ALT + Q to return to your workbook.
Enter the formula as instructed above.
Gord Dibben Excel MVP
Gord Dibben MS Excel MVP
On Mon, 3 Jul 2006 20:49:06 -0500, davfin
wrote:
Hi,
Excel 2003 question:
I have a sheet that contains text in consecutive cells (in a column),
I
need to copy all the consecutive cells' texts into one cell in a
separate spreadsheet.
Is there any quick way to do this? Just selecting the individual
texts
and copy/pasting is time consuming and a pain. I have multiple
occurrences of this and need to find a quick way.
All help and suggestions very much appreciated.
Thanks,
Dave.
Gord Dibben MS Excel MVP
--
davfin
------------------------------------------------------------------------
davfin's Profile:
http://www.excelforum.com/member.php...o&userid=36012
View this thread:
http://www.excelforum.com/showthread...hreadid=557961