VBA for formatting line breaks

[afrobea_r]

Dear Excel Community,

Currently I have multiple cells with data arranged like the example below:
[TABLE="width: 252"]
<tbody>[TR]
[TD]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

[/TD]
[/TR]
</tbody>[/TABLE]

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