I have data in multiple cells in column A:

A
1 10.21.101.12
2 DATA DATAPROC
3 SENDSITE
4 D /
5 CD SYS/DATA
6 LCD 'TAXCOLL.PROD.EXTRACT.FIRST'
7 PUT DOWNLOAD DEEDING1.TXT
8 LCD 'TAXCOLL.PROD.EXTRACT.SECOND'
9 PUT DOWNLOAD DEEDING2.TXT
10 CLOSE
11 QUIT

How can I put all of this data into one cell?

The long way. . . . .

=a1&", "&a2&", "&a3 etc for comma/space delimited

The short way. . . . . .

=concatrange(A1:A11)

Function ConCatRange(CellBlock As Range) As String
'for non-contiguous cells =ccr((a1:a10,c4,c6,e1:e5))
'note double parens
Dim cell As Range
Dim sbuf As String
For Each cell In CellBlock
On Error GoTo fred
If Len(cell.text) 0 Then sbuf = sbuf & cell.text & ", "
Next
ConCatRange = Left(sbuf, Len(sbuf) - 2)
fred:
End Function

Gord


On Mon, 17 Nov 2014 12:33:41 -0800 (PST), GWC
wrote:

I have data in multiple cells in column A:

A
1 10.21.101.12
2 DATA DATAPROC
3 SENDSITE
4 D /
5 CD SYS/DATA
6 LCD 'TAXCOLL.PROD.EXTRACT.FIRST'
7 PUT DOWNLOAD DEEDING1.TXT
8 LCD 'TAXCOLL.PROD.EXTRACT.SECOND'
9 PUT DOWNLOAD DEEDING2.TXT
10 CLOSE
11 QUIT

How can I put all of this data into one cell?

My original spreadsheet had data in a1 thru k8.

I've concatenated a1 thru a11 so the resulting cell has 11 lines.

Now how do I get the other cells (b1 thru k1) to be the same size as the cell with the concatenated data?

Concatenate means place all the data in one cell.

If you have placed it all in A1 then simply set A1 to wrap text


Gord

On Fri, 21 Nov 2014 06:04:55 -0800 (PST), GWC
wrote:

My original spreadsheet had data in a1 thru k8.

I've concatenated a1 thru a11 so the resulting cell has 11 lines.

Now how do I get the other cells (b1 thru k1) to be the same size as the cell with the concatenated data?

I used ALT+enter to get the data onto separate lines in A1.

On Friday, November 21, 2014 11:11:17 AM UTC-5, GWC wrote:
I used ALT+enter to get the data onto separate lines in A1.

Instead of ALT entering repeatedly you could modify Gord's line

If Len(cell.text) 0 Then sbuf = sbuf & cell.text & ", "

to be

If Len(cell.text) 0 Then sbuf = sbuf & cell.text & vbLf

If the cell is formatted to wrap text is should work for you.

Good luck.
Ken

A1 contains 11 lines. Other cells in row A contain one to 10 lines. How can I get all cells in Row A to have the same row-height as A1?

Hi,

Am Sat, 22 Nov 2014 10:12:55 -0800 (PST) schrieb GWC:

A1 contains 11 lines. Other cells in row A contain one to 10 lines. How can I get all cells in Row A to have the same row-height as A1?

try:

Sub height()
Dim LRow As Long

With ActiveSheet
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
.Range("A2:A" & LRow).RowHeight = _
.Range("A1").RowHeight
End With
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

I guess you assumed he meant column A instead of row A. I was wondering how cells in row have can have a different row height than cell A1. I was thinking he wanted extra line feeds in the cells, but, on further consideration that seems unlikely. I suppose we will hear from him again if your assumption was not correct.


On Saturday, November 22, 2014 1:23:28 PM UTC-5, Claus Busch wrote:
Hi,

Am Sat, 22 Nov 2014 10:12:55 -0800 (PST) schrieb GWC:

A1 contains 11 lines. Other cells in row A contain one to 10 lines. How can I get all cells in Row A to have the same row-height as A1?

try:

Sub height()
Dim LRow As Long

With ActiveSheet
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
.Range("A2:A" & LRow).RowHeight = _
.Range("A1").RowHeight
End With
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional