Option Explicit
Sub RemoveLineBreaks()
  'Looks at row 1 to find 2 columns and then runs a script to remove
  'all line breaks from the range
  Dim CheckRow As Long, TotalRows As Long
  Dim BadData As Variant, x As Integer
  Dim ICol As Integer, NCol As Integer
  Dim CheckNstring As String, CheckIstring As String
  
  'Set the variables and find the needed columns
  
    TotalRows = Range("A50000").End(xlUp).Row
    On Error Resume Next
    ICol = Range("1:1").Find("Items", LookAt:=xlWhole).Column
    NCol = Range("1:1").Find("No.", LookAt:=xlWhole).Column
    'Check if column numbers were found
    If ICol = 0 Then
      MsgBox ("Cannot find the term 'Items' in Row 1." & vbLf & "Please update and try again.")
      End
    End If
    If NCol = 0 Then
      MsgBox ("Cannot find the term 'No.' in Row 1." & vbLf & "Please update and try again.")
      End
    End If
    
    On Error GoTo 0 'Reset error handling
    BadData = Array(vbCr, vbLf, vbCrLf, Chr(10), Chr(13))
     
    For CheckRow = 2 To TotalRows
      CheckIstring = Cells(CheckRow, ICol).Value
      CheckNstring = Cells(CheckRow, NCol).Value
      For x = 0 To UBound(BadData)
        CheckIstring = WorksheetFunction.Substitute(CheckIstring, BadData(x), " ")
        CheckNstring = WorksheetFunction.Substitute(CheckNstring, BadData(x), " ")
      Next x
      Cells(CheckRow, ICol).Value = Trim(CheckIstring)
      Cells(CheckRow, NCol).Value = Trim(CheckNstring)
      
      'Clean up Vars
      CheckIstring = ""
      CheckNstring = ""
      Debug.Print TotalRows - CheckRow & " Records Left."
    Next CheckRow
    
    MsgBox "Complete"
End Sub