combine multiple rows of data into one row.
The single "A" in the LastRow=... statement could be replaced with
Source.Column
Otherwise that statement will also have to be modified if you have
another source location than in column A.
It was supposed to have been... I missed it when I converted my hard-coded
references (used to test out the concept behind the macro's code) to the
relative references I eventually changed them to. Thanks for catching that
oversight for me. For those following this thread, here is the corrected
code (so you can copy/paste it if desired)...
Sub CombineToSentences()
Dim X As Long, LastRow As Long
Dim AllWords As String, Words() As String, Sentences() As String
Dim Source As Range, Destination As Range
Set Source = Worksheets("Sheet1").Range("A2")
Set Destination = Worksheets("Sheet2").Range("B3")
With Source.Parent
LastRow = .Cells(.Rows.Count, Source.Column).End(xlUp).Row
ReDim Words(0 To LastRow - 1)
For X = 0 To LastRow - Source.Row
Words(X) = Source.Offset(X).Value
Next
End With
AllWords = Join(Words)
Sentences = Split(AllWords, "")
For X = 1 To UBound(Sentences)
Destination.Offset(X - 1).Value = "" & Sentences(X)
Next
End Sub
My same comment from my first posting about removing the "" &
(greater than symbol.. space... ampersand) from the statement in the last
For..Next loop still applies.
--
Rick (MVP - Excel)
While I know you have already have a solution to your problem, I thought
you
(and others reading this thread) might be interested in a totally
different
approach to doing what you asked. Just change the worksheet and range
references for the two Set statements to the source of your data and the
destination the "sentences" are to be placed at in this macro...
Sub CombineToSentences()
Dim X As Long, LastRow As Long
Dim AllWords As String, Words() As String, Sentences() As String
Dim Source As Range, Destination As Range
Set Source = Worksheets("Sheet1").Range("A2")
Set Destination = Worksheets("Sheet2").Range("B3")
With Source.Parent
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
ReDim Words(0 To LastRow - 1)
For X = 0 To LastRow - Source.Row
Words(X) = Source.Offset(X).Value
Next
End With
AllWords = Join(Words)
Sentences = Split(AllWords, "")
For X = 1 To UBound(Sentences)
Destination.Offset(X - 1).Value = "" & Sentences(X)
Next
End Sub
By the way, if your "sentences" are to be *real* sentences and they do not
need to preserve the greater than () symbol, just use this statement
inside
the last For..Next loop in place of the statement I have there now...
Destination.Offset(X - 1).Value = Sentences(X)
|