Solved: Merge Rows Problem

[snoopies]

Hi all,

I wrote a simple code to merge rows, but it causes the problem when having blank rows. I've attached a file to explain what I want to do..Thank you for kind attention!! :bow:


Public Sub MergeRows()
Set myRange = Range("A1:A50")
For Each c In myRange
jText = jText & "+" & c
Next
Range("B2") = jText

End Sub


[vba]
PROBLEM+1+2+3+4+5+6+7+8++++++14+15+16+17+18+19+20+21+22+23+24+25+26+++++31+ 32+33+34+35+36+37+38+39+40+41+42+43+44+45+46+47+48+49+50

[Ken Puls]

You just need to add a quick test to see if c has anything in it before you append the info to the string:

Public Sub MergeRows()

Set myRange = Range("A1:A50")
For Each c In myRange

If Not Len(c) = 0 Then jText = jText & "+" & c

Next
Range("B2") = jText

End Sub

HTH,

[snoopies]

Hi, Thanks for your reply.

There's still a problem when I follow the above code.
I want to divide it into 3 different sentences, not one.
e.g in my example, I want the following 3 sentences,

1+2+3+4+5+6+7+8
4+15+16+17+18+19+20+21+22+23+24+25+26
31+32+33+34+35+36+37+38+39+40+41+42+43+44+45+46+47+48+49+50

Is it possible?

[mdmackillop]

Adding to Ken's code


Public Sub MergeRows()
Dim MyRange As Range, c As Range
Dim JText As String
Set MyRange = Range("A1:A50")
For Each c In MyRange
If Not Len(c) = 0 Then
JText = JText & "+" & c
Else
If Right(JText, 1) <> Chr(10) Then JText = JText & Chr(10)
End If
Next
Range("B2") = JText
End Sub