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!

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!

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.