Microsoft Office Forums Run Script to remove carriage returns on certain columns

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-19-2014, 06:03 PM
ryanjohnsond@gmail.com ryanjohnsond@gmail.com is offline Run Script to remove carriage returns on certain columns Windows 8 Run Script to remove carriage returns on certain columns Office 2007
Novice
Run Script to remove carriage returns on certain columns
 
Join Date: Aug 2014
Posts: 18
ryanjohnsond@gmail.com is on a distinguished road
Default Run Script to remove carriage returns on certain columns

  1. How do I modify the Alexander Frolov VB code below to remove carriage returns on specific columns (i.e., with names, "Items" and "Sheet No.") and replace the carriage returns with commas (",")?
  2. I have about 50 Excel files like this. Is there a bulk way to run a script on all those files?

Code:
Sub RemoveCarriageReturns()
    Dim MyRange As Range
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
 
    For Each MyRange In ActiveSheet.UsedRange
        If 0 < InStr(MyRange, Chr(10)) Then
            MyRange = Replace(MyRange, Chr(10), ",")
        End If
    Next
 
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub
The result should look like this:

| Sheet No.|
-----------
| 1 |
| 1 |
------------

(removes carriage return and replaces with comma) in column "


| Sheet No.|
-----------
| 1,1 |
------------
Reply With Quote
  #2  
Old 08-19-2014, 10:09 PM
excelledsoftware excelledsoftware is offline Run Script to remove carriage returns on certain columns Windows 7 64bit Run Script to remove carriage returns on certain columns Office 2003
IT Specialist
 
Join Date: Jan 2012
Location: Utah
Posts: 453
excelledsoftware will become famous soon enough
Default

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.
Reply With Quote
  #3  
Old 08-20-2014, 09:02 AM
ryanjohnsond@gmail.com ryanjohnsond@gmail.com is offline Run Script to remove carriage returns on certain columns Windows 8 Run Script to remove carriage returns on certain columns Office 2007
Novice
Run Script to remove carriage returns on certain columns
 
Join Date: Aug 2014
Posts: 18
ryanjohnsond@gmail.com is on a distinguished road
Default

Quote:
Originally Posted by excelledsoftware View Post
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!
Reply With Quote
  #4  
Old 08-20-2014, 10:10 AM
ryanjohnsond@gmail.com ryanjohnsond@gmail.com is offline Run Script to remove carriage returns on certain columns Windows 8 Run Script to remove carriage returns on certain columns Office 2007
Novice
Run Script to remove carriage returns on certain columns
 
Join Date: Aug 2014
Posts: 18
ryanjohnsond@gmail.com is on a distinguished road
Default

Quote:
Originally Posted by excelledsoftware View Post
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?
Reply With Quote
  #5  
Old 08-20-2014, 11:53 AM
ryanjohnsond@gmail.com ryanjohnsond@gmail.com is offline Run Script to remove carriage returns on certain columns Windows 8 Run Script to remove carriage returns on certain columns Office 2007
Novice
Run Script to remove carriage returns on certain columns
 
Join Date: Aug 2014
Posts: 18
ryanjohnsond@gmail.com is on a distinguished road
Default

How do you do a Find and Replace on a column header with a carriage return in it?
Blatt
Sheet
Reply With Quote
  #6  
Old 08-20-2014, 10:56 PM
excelledsoftware excelledsoftware is offline Run Script to remove carriage returns on certain columns Windows 7 64bit Run Script to remove carriage returns on certain columns Office 2003
IT Specialist
 
Join Date: Jan 2012
Location: Utah
Posts: 453
excelledsoftware will become famous soon enough
Default

Quote:
Originally Posted by ryanjohnsond@gmail.com View Post
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

Last edited by excelledsoftware; 08-20-2014 at 11:00 PM. Reason: Incorrect variable
Reply With Quote
  #7  
Old 08-20-2014, 11:07 PM
excelledsoftware excelledsoftware is offline Run Script to remove carriage returns on certain columns Windows 7 64bit Run Script to remove carriage returns on certain columns Office 2003
IT Specialist
 
Join Date: Jan 2012
Location: Utah
Posts: 453
excelledsoftware will become famous soon enough
Default

Quote:
Originally Posted by ryanjohnsond@gmail.com View Post
[LIST=1]
I have about 50 Excel files like this. Is there a bulk way to run a script on all those files?
There is a way to run code with all workbooks. I can modify the code I sent you to do this however it is important that when running code on multiple workbooks that they each have similar attributes.

Do all of the workbooks have multiple sheets that need this ran or just the first sheet?
Are all of the workbooks a certain type of name so the code can skip over workbooks that dont meet that criteria?
Are all of the workbooks in one folder? I can research how to get Excel to open and process each file from a folder but the first 2 questions would need to be answered before we do that.

I can write something that will run in all OPEN workbooks pretty easily but you will probably not want to open up 50+ workbooks on your computer.

Thanks
Reply With Quote
  #8  
Old 08-20-2014, 11:19 PM
ryanjohnsond@gmail.com ryanjohnsond@gmail.com is offline Run Script to remove carriage returns on certain columns Windows 8 Run Script to remove carriage returns on certain columns Office 2007
Novice
Run Script to remove carriage returns on certain columns
 
Join Date: Aug 2014
Posts: 18
ryanjohnsond@gmail.com is on a distinguished road
Default

Quote:
Originally Posted by excelledsoftware View Post
There is a way to run code with all workbooks. I can modify the code I sent you to do this however it is important that when running code on multiple workbooks that they each have similar attributes.

Do all of the workbooks have multiple sheets that need this ran or just the first sheet?
Are all of the workbooks a certain type of name so the code can skip over workbooks that dont meet that criteria?
Are all of the workbooks in one folder? I can research how to get Excel to open and process each file from a folder but the first 2 questions would need to be answered before we do that.

I can write something that will run in all OPEN workbooks pretty easily but you will probably not want to open up 50+ workbooks on your computer.

Thanks
That's fantastic!

>> I can modify the code I sent you to do this however it is important that when running code on multiple workbooks that they each have similar attributes. >>

They all have similar attributes, except the columns start on the 2nd row, and the two columns are the 2nd and 3rd columns.

One other caveat. The column names in the column headers have a carriage return in them (in the header itself), like "Part [carriage return] Number". So, right now, I have to manually rename the headers so that the code finds them. Is there a way to remove the carriage off the header name, rename it without a comma (the name is all on one line) and then do the rest of the removing carriage returns and replacing it with commas?

So the header looks like this:

Part
Number

and needs to look like this (VB script needs to remove the carriage, bring the word "Number" up on the same line as "Part":

Part Number

and then the rest of your code runs...

>>Do all of the workbooks have multiple sheets that need this ran or just the first sheet?>>
Not sure what you mean by "multiple sheets". I am bringing PDFs into Excel and then running the script so a DB can use certain columns for data on a web page. When you say "sheet", do you you mean multiple tabs at the bottom of an Excel file?

>> Are all of the workbooks a certain type of name so the code can skip over workbooks that dont meet that criteria? >>
They're named by part number and part name (numbers first, then name second in one file name string... like this: 42A 500 005 H 006 H Rear axle.pdf, 42A 500 007 H 008 H Wheel carrier assembly for rear axle.pdf, 42A 500 009 F Stabilizer rear axle.pdf, 42A 513 005 F 006 F Shock absorber, rear axle.pdf, etc

>> Are all of the workbooks in one folder? I can research how to get Excel to open and process each file from a folder but the first 2 questions would need to be answered before we do that. >>

There are two separate folders, but I can cut 'n' paste into different folders to reduce the number of files open at a time.

Thank you very much, excelledsoftware.
Reply With Quote
  #9  
Old 08-20-2014, 11:48 PM
excelledsoftware excelledsoftware is offline Run Script to remove carriage returns on certain columns Windows 7 64bit Run Script to remove carriage returns on certain columns Office 2003
IT Specialist
 
Join Date: Jan 2012
Location: Utah
Posts: 453
excelledsoftware will become famous soon enough
Default

Yes this code should do that.
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, CheckCol As Integer
  Dim CheckNstring As String, CheckIstring As String, CheckColString As String
  Dim NewNstring As String, NewIstring 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))
    
    
    'Fix the column headers
    For CheckCol = 1 To 255
      CheckColString = Cells(1, CheckCol).Value
      For x = 0 To UBound(BadData)
        CheckColString = WorksheetFunction.Substitute(CheckColString, BadData(x), " ")
      Next x
      Cells(1, CheckCol).Value = Trim(CheckColString)
      If Cells(1, CheckCol).Value = "" Then Exit For 'Exit loop when there is no more data
      CheckColString = ""
    Next CheckCol

    'Check the 2 columns
    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 = CheckIstring
      Cells(CheckRow, NCol).Value = CheckNstring
      
      'Clean up Vars
      CheckIstring = ""
      CheckNstring = ""
      Debug.Print TotalRows - CheckRow & " Records Left."
    Next CheckRow
    
    MsgBox "Complete"
End Sub
My last code I posted before this replaced breaks with a space I forgot the comma so ignore that code and just use this one.

As for the multiple workbooks I will have a few more questions for you to get that set up but this code should run on any workbook no problem.

Let me know if you have any questions.

Thanks
Reply With Quote
  #10  
Old 08-21-2014, 01:35 PM
ryanjohnsond@gmail.com ryanjohnsond@gmail.com is offline Run Script to remove carriage returns on certain columns Windows 8 Run Script to remove carriage returns on certain columns Office 2007
Novice
Run Script to remove carriage returns on certain columns
 
Join Date: Aug 2014
Posts: 18
ryanjohnsond@gmail.com is on a distinguished road
Default

Quote:
Originally Posted by excelledsoftware View Post
Yes this code should do that.
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, CheckCol As Integer
  Dim CheckNstring As String, CheckIstring As String, CheckColString As String
  Dim NewNstring As String, NewIstring As String
  
  'Set the variables and find the needed columns
  
    TotalRows = Range("A50000").End(xlUp).Row
    On Error Resume Next

      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))
    
    
    'Fix the column headers
    For CheckCol = 1 To 255
      CheckColString = Cells(1, CheckCol).Value
      For x = 0 To UBound(BadData)
        CheckColString = WorksheetFunction.Substitute(CheckColString, BadData(x), " ")
      Next x
      Cells(1, CheckCol).Value = Trim(CheckColString)
      If Cells(1, CheckCol).Value = "" Then Exit For 'Exit loop when there is no more data
      CheckColString = ""
    Next CheckCol

    'Check the 2 columns
    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 = CheckIstring
      Cells(CheckRow, NCol).Value = CheckNstring
      
      'Clean up Vars
      CheckIstring = ""
      CheckNstring = ""
      Debug.Print TotalRows - CheckRow & " Records Left."
    Next CheckRow
    
    MsgBox "Complete"
End Sub
My last code I posted before this replaced breaks with a space I forgot the comma so ignore that code and just use this one.

As for the multiple workbooks I will have a few more questions for you to get that set up but this code should run on any workbook no problem.

Let me know if you have any questions.

Thanks
How do I deal with a carriage return in the header? I didn't notice this, but on every file, the header is "items [carriage return] sheet", and "No. [carriage return] sheet".

To use your code I have to manually go into every header first and remove the carriage return so that your code can find header "items" and "no."

The header actually looks like this:

-------
items
sheet
-------

and

-------
no.
sheet
-------
What's the best way to deal with the carriage return that's forcing the words "sheet" to wrap down to the next line in the header cell?

Code:
    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.")
*UPDATE. Another thought.
The headers in question are always the 2nd and 3rd column on row 9. It's ok if it replaces the header with a comma too. Just as long as I can remove the carriage returns on their entire column that would be fine. Like this example below:

e.g.,

Start at row 9
<pre>
col1 col2 col3
---- ------- -------
Pos. Items No.
sheet sheet
---- ------- -------
</pre>
and from there remove carriage and replace with comma.
<pre>
col1 col2 col3
---- -------------- -----------
Pos. Items,sheet No.,sheet
---- ------------- -----------
</pre>
Reply With Quote
  #11  
Old 08-21-2014, 06:39 PM
excelledsoftware excelledsoftware is offline Run Script to remove carriage returns on certain columns Windows 7 64bit Run Script to remove carriage returns on certain columns Office 2003
IT Specialist
 
Join Date: Jan 2012
Location: Utah
Posts: 453
excelledsoftware will become famous soon enough
Default

The code takes the carriage returns out of the headers before it processes the columns. Now if header has other data in it besides No. or Item then yes that would need to be changed. At this point it would be best if you would post a sample sheet so I can write what is needed to that.
Reply With Quote
  #12  
Old 08-21-2014, 09:47 PM
ryanjohnsond@gmail.com ryanjohnsond@gmail.com is offline Run Script to remove carriage returns on certain columns Windows 8 Run Script to remove carriage returns on certain columns Office 2007
Novice
Run Script to remove carriage returns on certain columns
 
Join Date: Aug 2014
Posts: 18
ryanjohnsond@gmail.com is on a distinguished road
Default

Quote:
Originally Posted by excelledsoftware View Post
The code takes the carriage returns out of the headers before it processes the columns. Now if header has other data in it besides No. or Item then yes that would need to be changed. At this point it would be best if you would post a sample sheet so I can write what is needed to that.
Here you go: a cleaned up template I inherited for work.
Download Template Here

As you can see (from the Excel file, once you open it) row 9, column 2 and 3, their headers have a carriage return on them. Again, thank you very much for helping like this.

Last edited by ryanjohnsond@gmail.com; 08-21-2014 at 10:54 PM.
Reply With Quote
  #13  
Old 08-21-2014, 11:25 PM
excelledsoftware excelledsoftware is offline Run Script to remove carriage returns on certain columns Windows 7 64bit Run Script to remove carriage returns on certain columns Office 2003
IT Specialist
 
Join Date: Jan 2012
Location: Utah
Posts: 453
excelledsoftware will become famous soon enough
Default

Ok thanks for posting this.

The sheet has several tables. When you said headers one would assume you mean the first row but that appears to not be the case.

Now before I get into this over the weekend I need to know if all you are trying to do is identify the 2 columns that have Items Sheet, and no. items and then replace the carriage return with a comma. If thats all we need to do no problem I can have it written pretty quickly. If that is not all then I need another spreadsheet attached that has a good amount of data (not a blank template) and instructions of what you want.

Use the attachment button on this form and attach a workbook with the needed info if necessary.

Let me know and I will get started on it.

Thanks
Reply With Quote
  #14  
Old 08-22-2014, 10:11 AM
ryanjohnsond@gmail.com ryanjohnsond@gmail.com is offline Run Script to remove carriage returns on certain columns Windows 8 Run Script to remove carriage returns on certain columns Office 2007
Novice
Run Script to remove carriage returns on certain columns
 
Join Date: Aug 2014
Posts: 18
ryanjohnsond@gmail.com is on a distinguished road
Default

Quote:
Originally Posted by excelledsoftware View Post
Ok thanks for posting this.
if all you are trying to do is identify the 2 columns that have Items Sheet, and no. items and then replace the carriage return with a comma. If that's all we need to do no problem I can have it written pretty quickly.
File attached.

Yes. That's basically all I am trying to do. But after you got the code to run, I noticed the headers actually had a carraige return too, and I had to manually going into the headers and remove the carriages before the code could work. That was my oversight. Sorry about that.

Don't work on the weekend for this; I would feel bad. You've done heroic work, man. Thank you very much again for your time and effort.

Related question: Is there a way to open all the Excel files into Excel into one window, and run a batch on them with your code? Oh, BTW. That's the naming convention for all the files: numbers, then part name, and then attribute (e.g, "left" part, or "right", "back" or "front")
Attached Files
File Type: xlsm 123 456 007 Z 001 A Part Name Attribute Name.xlsm (28.9 KB, 5 views)
Reply With Quote
  #15  
Old 08-25-2014, 09:17 PM
excelledsoftware excelledsoftware is offline Run Script to remove carriage returns on certain columns Windows 7 64bit Run Script to remove carriage returns on certain columns Office 2003
IT Specialist
 
Join Date: Jan 2012
Location: Utah
Posts: 453
excelledsoftware will become famous soon enough
Default

I did the research and know how to get this to work on every workbook in a folder. Now it will take some time to code but I still think I dont have all the information. The last attachment contains just sample data with the only carriage returns showing in the headers. I need to know where else to have the carriage returns removed. since this code will process 50 workbooks no problem I want to write it once and make sure it is right.

Thanks
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Run Script to remove carriage returns on certain columns stop carriage return or enter key in a table Alaska1 Word 1 01-14-2013 08:48 AM
Run Script to remove carriage returns on certain columns Coding into a macro a carriage return sinbad Word VBA 6 02-27-2012 03:51 AM
Run Script to remove carriage returns on certain columns 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
Run Script to remove carriage returns on certain columns Table of contents, remove spacing and put in 2 columns - need help urgently! nam085 Word Tables 1 03-04-2010 08:00 AM


All times are GMT -7. The time now is 07:25 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft