how to remove linebreaks in cells with VBA

[Ozzmantsje]

I have some excel files that I save as comma delimited text files in order to
import them into a Mysql database.
However, some text cells have linebreaks in them, which results in the part
after the linebreak being put onto a new line in the comma delimited text
file.

The mysql import script treats it as a new record, which should not be the
case.
So I want to make a macro that checks all the cells and removes any
linebreaks in them. Can anyone show me how to do that?

Thanks.

 

[Norman Jones]

Hi Ozzmantsje,

Try somethinhg like:

'=============>>
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet

Set WB = Workbooks("MyBook.xls") '<<==== CHANGE
Set SH = WB.Sheets("Sheet1") '<<==== CHANGE

SH.Cells.Replace What:=Chr(10), _
Replacement:=vbNullString, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
End Sub
'<<=============