Microsoft Office Forums - View Single Post - [Solved] Run Script to remove carriage returns on certain columns

excelledsoftware · 08-20-2014, 10:56 PM

Quote:

Originally Posted by ryanjohnsond@gmail.com

I have two columns named, "Items" and "No." How do I run your script just on those two columns, and leave the other columns untouched by the script?

Thanks!

Assuming that your worksheet has the headers in row 1 and is not larger than 50,000 rows, the following code should work and replace your current code.

Code:

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