VBA for formatting line breaks

[afrobea_r]

Dear Excel Community,

Currently I have multiple cells with data arranged like the example below:

Apple Orange Watermelon & Mangosteen Using this formula : For Each UsedCell In Selection Cells.Replace What:=Chr(10), Replacement:=" " My data gets rearranged to: AppleOrangeWatermelon &Mangosteen How can I modify the formula to arrange data in this manner instead. Apple Orange Watermelon & Mangosteen

<tbody>
</tbody>

Essentially I would like to retain the existing paragraphing format but without the level spaces.

Help appreciated!

Thank you!

 

[steve the fish]

Try:

Selection.Replace What:=Chr(10) & Chr(10), Replacement:=Chr(10), LookAt:=xlPart

 

[afrobea_r]

Thank U Sir, worked like a charm. Much appreciated!

 

[afrobea_r]

Try:

Selection.Replace What:=Chr(10) & Chr(10), Replacement:=Chr(10), LookAt:=xlPart

Btw Sir,

If I wish to combine the conditional formatting based on values assigned to adjacent cells, is this correct?

Sub CombinedFormat()
' test Macro
With Range("L12").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWindow.SmallScroll Down:=3


If Range("Q3") = "2" Then


Cells.Replace What:=Chr(10) & Chr(10), Replacement:=Chr(10), LookAt:=xlPart


End If


If Range("Q3") = "1" Then


Cells.Replace What:=Chr(10), Replacement:=" "


End If


End With


End Sub