How to remove newlines (carriage returns) by search and replace

Posted by Dean Hung on October 10, 2000 5:40 PM

Hi,

Does anyone know how to remove newlines (carriage returns) using search and replace? I know that you can type ALT+ENTER to create a newline in a cell; I would like to find out how you can easily get rid of newlines in a spreadsheet.

Thanks so much!

Posted by Ivan Moala on October 11, 2000 4:28 AM

I don't think you can do this via the Find/replace
But you can do this via code.

'Created by Chip Pearson
'Cleans up data by removing tabs and carriage returns in worksheet cells.

Sub CleanUp()
Dim TheCell As Range
For Each TheCell In ActiveSheet.UsedRange
With TheCell
If .HasFormula = False Then
.Value = Application.WorksheetFunction.Clean(.Value)
End If
End With
Next TheCell
End Sub

HTH

Ivan