#1
|
|||
|
|||
Run Script to remove carriage returns on certain columns
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 | Sheet No.| ----------- | 1 | | 1 | ------------ (removes carriage return and replaces with comma) in column " | Sheet No.| ----------- | 1,1 | ------------ |
#2
|
|||
|
|||
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 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. |
#3
|
|||
|
|||
Quote:
Thanks! |
#4
|
|||
|
|||
Quote:
|
#5
|
|||
|
|||
How do you do a Find and Replace on a column header with a carriage return in it?
Blatt Sheet |
#6
|
|||
|
|||
Quote:
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 |
#7
|
|||
|
|||
Quote:
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 |
#8
|
|||
|
|||
Quote:
>> 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. |
#9
|
|||
|
|||
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 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 |
#10
|
|||
|
|||
Quote:
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.") 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> |
#11
|
|||
|
|||
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.
|
#12
|
|||
|
|||
Quote:
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. |
#13
|
|||
|
|||
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 |
#14
|
|||
|
|||
Quote:
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") |
#15
|
|||
|
|||
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 |
|
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 |