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