Run Script to remove carriage returns on certain columns

excelledsoftware · #1 08-19-2014, 10:09 PM

In my experience the line breaks and carriage returns take many names. I have found that if I try to replace just one type it dosnt get them all. The code below works very well to remove ALL line breaks or carriage returns.

Code:

Dim v as Variant, BadData as Variant

  
  BadData = Array (vblf, vbcrlf, vbcr, Chr(10), Chr(13))
  For Each v in BadData
    MyRange = Replace(MyRange, v, ",")
  Next v

or you can use

Code:

Dim v as Integer, BadData as Variant

  
  BadData = Array (vblf, vbcrlf, vbcr, Chr(10), Chr(13))
  For v = 0 to ubound(BadData)
    MyRange = Replace(MyRange, BadData(v), ",")
  Next v

the 2nd one uses less memory.

ryanjohnsond@gmail.com · #2 08-20-2014, 09:02 AM

Quote:

Originally Posted by excelledsoftware

The code below works very well to remove ALL line breaks or carriage returns.

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!

excelledsoftware · #3 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

ryanjohnsond@gmail.com · #4 08-20-2014, 10:10 AM

Quote:

Originally Posted by excelledsoftware

In my experience the line breaks and carriage returns take many names. I have found that if I try to replace just one type it dosnt get them all. The code below works very well to remove ALL line breaks or carriage returns.

Code:

Dim v as Variant, BadData as Variant

  
  BadData = Array (vblf, vbcrlf, vbcr, Chr(10), Chr(13))
  For Each v in BadData
    MyRange = Replace(MyRange, v, ",")
  Next v

or you can use

Code:

Dim v as Integer, BadData as Variant

  
  BadData = Array (vblf, vbcrlf, vbcr, Chr(10), Chr(13))
  For v = 0 to ubound(BadData)
    MyRange = Replace(MyRange, BadData(v), ",")
  Next v

the 2nd one uses less memory.

Didn't work. Should your code be placed inside my existing code?

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
stop carriage return or enter key in a table	Alaska1	Word	1	01-14-2013 08:48 AM
Coding into a macro a carriage return	sinbad	Word VBA	6	02-27-2012 03:51 AM
Paragraph (carriage) return font size	revrossreddick	Word	2	12-28-2011 01:33 PM
Carriage Return Help	UCHelp	Word	1	04-04-2010 10:11 PM
Table of contents, remove spacing and put in 2 columns - need help urgently!	nam085	Word	1	03-04-2010 08:00 AM