Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3
Default Concatenate a Range of Cells

I would like to come up with a macro to concatenate a range of cells like
A1:B3 into cell A4 (first row of range, last column of range plus one). I
would like to add a carriage return after concatenating each row. So in
this example A4 would be equal to =CONCATENATE(A1,A2,A3,CHAR(10),B1,B2,B3)

I would like to select the cells and then run the macro.

Any ideas would be greatly appreciated. Thanks...


  #2   Report Post  
Posted to microsoft.public.excel.programming
JMB JMB is offline
external usenet poster
 
Posts: 2,062
Default Concatenate a Range of Cells

I'm a little unclear on where you want the data to end up (example is
A4-which is last row + 1, first column, but you said first row, last column +
1), so I went with last row + 1, first column.

Does this help?

Sub JoinText()
Dim Col As Range
Dim Cell As Range
Dim Result As String

With Selection
For Each Col In .Columns
For Each Cell In Col.Cells
Result = Result & Cell.Value
Next Cell
If Col.Column < .Columns(.Columns.Count).Column Then _
Result = Result & Chr(10)
Next Col
Cells(.Rows(.Rows.Count).Row + 1, _
.Columns(1).Column).Value = Result
End With
End Sub



"Bob Stearns" wrote:

I would like to come up with a macro to concatenate a range of cells like
A1:B3 into cell A4 (first row of range, last column of range plus one). I
would like to add a carriage return after concatenating each row. So in
this example A4 would be equal to =CONCATENATE(A1,A2,A3,CHAR(10),B1,B2,B3)

I would like to select the cells and then run the macro.

Any ideas would be greatly appreciated. Thanks...



  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3
Default Concatenate a Range of Cells

Very close to what I want except I want to go row by row instead of column
by column. Like this:

Column A B C D
Row 1 I just want it
Row 2 to read this way

I want to put the result in the first row of the selection in the next
unused column in this case E1. E1 would contain
"I just want it" & CHAR(10) & "to read this way"

Thank you very much!

---


"JMB" wrote in message
...
I'm a little unclear on where you want the data to end up (example is
A4-which is last row + 1, first column, but you said first row, last
column +
1), so I went with last row + 1, first column.

Does this help?

Sub JoinText()
Dim Col As Range
Dim Cell As Range
Dim Result As String

With Selection
For Each Col In .Columns
For Each Cell In Col.Cells
Result = Result & Cell.Value
Next Cell
If Col.Column < .Columns(.Columns.Count).Column Then _
Result = Result & Chr(10)
Next Col
Cells(.Rows(.Rows.Count).Row + 1, _
.Columns(1).Column).Value = Result
End With
End Sub



"Bob Stearns" wrote:

I would like to come up with a macro to concatenate a range of cells like
A1:B3 into cell A4 (first row of range, last column of range plus one).
I
would like to add a carriage return after concatenating each row. So in
this example A4 would be equal to
=CONCATENATE(A1,A2,A3,CHAR(10),B1,B2,B3)

I would like to select the cells and then run the macro.

Any ideas would be greatly appreciated. Thanks...





  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Concatenate a Range of Cells


Hello Bob,

Here is a different version take puts the text in rows.

Public Sub ConcatenateRows()

Dim FirstColumn
Dim ColumnCount
Dim FirstRow
Dim LastRow
Dim N
Dim NewCell As Range

With Selection
FirstColumn = .Item(1).Column
ColumnCount = .Columns.Count
FirstRow = .Item(1).Row
LastRow = .Rows.Count + FirstRow - 1
End With

Set NewCell = ActiveSheet.Cells(LastRow + 1, FirstColumn)

For Each Cell In Selection
N = N + 1
NewCell = NewCell & " " & Cell.Value
If N = ColumnCount Then
NewCell = NewCell & vbLf
N = 0
End If
Next Cell

End Sub

Sincerely,
Leith Ros

--
Leith Ros
-----------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...fo&userid=1846
View this thread: http://www.excelforum.com/showthread.php?threadid=50558

  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3
Default Concatenate a Range of Cells

Leith,

I am using a combination of your and JMB's suggestions. This does what I
need.

Thanks...

---

Public Sub JoinCells()

Dim row_num As Range
Dim cell As Range
Dim result As String

With Selection
For Each row_num In .Rows
For Each cell In row_num.Cells
result = result & cell.Value & " "
Next cell
If row_num.Row < .Rows(.Rows.Count).Row Then result = result & Chr(10)
Next row_num
End With

With Cells(Selection.Row, Selection.Column + Selection.Columns.Count)
.Value = result
.WrapText = True
End With

End Sub


"Leith Ross" wrote
in message ...

Hello Bob,

Here is a different version take puts the text in rows.

Public Sub ConcatenateRows()

Dim FirstColumn
Dim ColumnCount
Dim FirstRow
Dim LastRow
Dim N
Dim NewCell As Range

With Selection
FirstColumn = .Item(1).Column
ColumnCount = .Columns.Count
FirstRow = .Item(1).Row
LastRow = .Rows.Count + FirstRow - 1
End With

Set NewCell = ActiveSheet.Cells(LastRow + 1, FirstColumn)

For Each Cell In Selection
N = N + 1
NewCell = NewCell & " " & Cell.Value
If N = ColumnCount Then
NewCell = NewCell & vbLf
N = 0
End If
Next Cell

End Sub

Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile:
http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=505582



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Function to concatenate cells in a range Leporello Excel Worksheet Functions 3 May 12th 23 07:42 PM
concatenate with a varying range of cells Riversage Excel Worksheet Functions 0 January 29th 07 08:43 PM
concatenate cells by a given range Twan Kennis Excel Worksheet Functions 4 July 1st 06 12:54 AM
How do I concatenate two currency cells to show a price range? nevermore627 Excel Worksheet Functions 5 June 30th 06 05:03 PM
How to concatenate adjacent cells in a range without using &? Ark Excel Worksheet Functions 4 October 16th 05 06:38 PM


All times are GMT +1. The time now is 07:30 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"