Multiple rows into one cell

[GWC]

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?

 

[Gord Dibben]

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

 

[GWC]

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?

 

[Gord Dibben]

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

 

[GWC]

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

 

[ken23508]

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

 

[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?

 

[Claus Busch]

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.

 

[ken23508]

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.