Multiple rows into one cell

  • Thread starter Thread starter GWC
  • Start date Start date
G

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?
 
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
 
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
 
I used ALT+enter to get the data onto separate lines in A1.
Click to expand...

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?
Click to expand...

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.
 
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.
 
Back
Top