Search and replace New line characters

R

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
 
B

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)
 
D

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.)
 
Top