VBA Code to Remove Carriage Returns

[bepedicino]

Hello,

I am using the following macro below with Excel 2010 and would like to include some VBA code that will trim all carriage returns from columns A, B, C, D, and E) prior to the file being saved at the end of the process. Can anyone please assist?

-----------------------------------------
Sub ReadyForUpload()

Const MyTarget = "#N/A" ' <-- change to suit

Dim Rng As Range, DelCol As New Collection, x
Dim i As Long, j As Long, k As Long

' Calc last row number
j = Cells.SpecialCells(xlCellTypeLastCell).Row 'can be: j = Range("C" & Rows.Count).End(xlUp).Row

' Collect rows range with MyTarget
For i = 1 To j
If WorksheetFunction.CountIf(Rows(i), MyTarget) > 0 Then
k = k + 1
If k = 1 Then
Set Rng = Rows(i)
Else
Set Rng = Union(Rng, Rows(i))
If k >= 100 Then
DelCol.Add Rng
k = 0
End If
End If
End If
Next
If k > 0 Then DelCol.Add Rng

' Turn off screen updating and events
Application.ScreenUpdating = False
Application.EnableEvents = False

' Delete rows with MyTarget
For Each x In DelCol
x.Delete
Next

' Update UsedRange
With ActiveSheet.UsedRange: End With

' Restore screen updating and events
Application.ScreenUpdating = True
Application.EnableEvents = True


With Application
.Calculate
.ScreenUpdating = False
.Calculation = xlCalculationManual
.DisplayAlerts = False
End With

With ActiveSheet
.Columns.Hidden = False
.Rows.Hidden = False
.UsedRange.Value = .UsedRange.Value
End With

For Each Worksheet In ThisWorkbook.Worksheets
If Worksheet.Name = ActiveSheet.Name Then
Else
Worksheet.Delete
End If
Next Worksheet

With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.DisplayAlerts = True
End With

Columns("U").NumberFormat = "@"

Columns("F").Delete

Columns("I").Delete

Const Ffold As String = "\\WS0113\WLDepts$\Administration\Trade Compliance\IT\Integration Point\Daily - Product Classification Upload\" 'change as required
Dim Fname As String

Fname = "Product Classification Upload"
Fname = Fname & " - " & Format(Date, "yyyymmdd") & ".xlsx"

Application.DisplayAlerts = False

ActiveWorkbook.SaveAs _
Filename:=Ffold & Application.PathSeparator & Fname, _
FileFormat:=xlOpenXMLWorkbook

Application.DisplayAlerts = True

End Sub

 

[Scott Huish]

You might only need the first one, but I have included the second as well, just in case. I replaced them with a space but you can change that to whatever you need.

Range("A:E").Replace Chr(10), " "
Range("A:E").Replace Chr(13), " "

 

[CharlesChuckieCharles]

This is the code

It replaces CHR(10) carriage return with space

Dim MyCell As Range
For Each MyCell In Range("A1:D100")
MyCell.Value = Replace(MyCell.Value, Chr(10), " ")
Next MyCell

 

[bepedicino]

Thank you to the both of you. However, one small question. Would the best way to remove (delete) the carriage return all together be to simply remove the space between the parenthesis, or is there a better preferred method?

Range("A:E").Replace Chr(10), ""
Range("A:E").Replace Chr(13), ""

 

[Scott Huish]

Yes, just like you have it. No space between the quotes.

 

[bepedicino]

Thank you again for your support!