CODEWITHSUNDEEP
vbaexcelvbscript
raju
raju

Reputation: 287

How to remove Carriage return values in excel?

I have code to remove carriage return values,it is giving my desired result but every time i have to open excel sheet and run macro.Can you please help me remove carriage return values for all files in a folder without opening excel file.

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

Thanks In Advance.

Upvotes: 0

Views: 579

Answers (1)

istrupin
istrupin

Reputation: 1543

It seems like what you're really after here is how to iterate through files in a folder, and then use your sub-routine on each one of those file. For the first part (iteration) take a look at this answer.

What you can then do is within the loop call your RemoveCarriageReturns() sub-routine. You'll have a nested loop with your current implementation, which isn't ideal, but if the performance hit isn't too big, you'll be alright. Alternatively, you can refactor your RemoveCarriageReturns() sub-routine to have less internal looping using something like this.

Upvotes: 1

Related Questions