Reputation: 287
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
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