how to remove linebreaks in cells with VBA

O

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.
 
N

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
'<<=============
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top