Run Script to remove carriage returns on certain columns

ryanjohnsond@gmail.com · #1 08-19-2014, 06:03 PM

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 (",")?
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 |
------------

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

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