Search and replace New line characters

[Rajiv Chandran]

how do I find out which cells contain new line characters.
I need to strip out all the newline characters from my sheet.

Thanks,
R

 

[Bob Phillips]

Rajiv,

This VBA might help

For Each cell In ActiveSheet.UsedRange
cell.Value = Replace(cell.Value, Chr(10), vbNull)
cell.Value = Replace(cell.Value, Chr(13), vbNull)
Next cell

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Dave Peterson]

You could get them all with the equivalent of Edit|replace|All:

Option Explicit
Sub testme()
ActiveSheet.Cells.Replace What:=Chr(10), _
Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End Sub

(I changed them to a space character. Adjust if required.)