Text into one cell
 

Hi!

I have text spread in several cells and want the text to be gathered into
one cell.
The cells with the text are in consecutive rows in one column. I want to
make a macro that gather the text from cells I have selected. How can I do
this

Ex.
Cell A1: "Microsoft"
Cell A2: "Visual"
Cell A3: "Basic"

I select cells A1:A3, run macro and the result is
Cell A1: "Microsoft Visual Basic"

Please help!

Text into one cell
 

Something like this, perhaps...? (Using Join() would be nicer, but I couldn't
figure out a way to do it. Hopefully someone else will be able to tell you
how to do that.)

Cheers,
/MP

Public Sub ConsolidateMyRange()
Dim t As String
Dim r As Range

' Pre-condition: we want to make sure we've got some
' sensible input before we start joining the values.
If (VarType(Application.Selection) < vbVariant + vbArray) Then
Exit Sub
End If

' Concatenate the values of all selected cells
For Each r In Application.Selection
t = t & r.Value & " "
Next r

' If there is something to output then assign it
' to the first selected cell (trim trailing space)
If Len(t) 0 Then
Application.Selection(1, 1).Value = Left(t, Len(t) - 1)
End If
End Sub



"Trond" wrote:

Hi!

I have text spread in several cells and want the text to be gathered into
one cell.
The cells with the text are in consecutive rows in one column. I want to
make a macro that gather the text from cells I have selected. How can I do
this

Ex.
Cell A1: "Microsoft"
Cell A2: "Visual"
Cell A3: "Basic"

I select cells A1:A3, run macro and the result is
Cell A1: "Microsoft Visual Basic"

Please help!

Text into one cell
 

Hi
This should do it

Public Sub tester()
Dim Testrange as Range, Cell as Range
Dim TestString as String
On Error Resume Next
Set Testrange = Selection
If Not Testrange Is Nothing Then
For Each Cell In Testrange
TestString = TestString & " " & Cell.Text
Next Cell
Testrange.Cells(1, 1).Value = Trim(TestString)
End If
Set Testrange = Nothing
End Sub

The error message bit is just in case you have graphic or some such
selected.
regards
Paul

Trond wrote:
Hi!

I have text spread in several cells and want the text to be gathered into
one cell.