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 |
------------

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