0

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.

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
raju
  • 287
  • 3
  • 5

1 Answers1

1

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.

istrupin
  • 1,423
  • 16
  • 32
  • All that said, this will still open each excel file in the folder -- it will just do it programmatically. I'm making the assumption that that's okay, even though you said you didn't want to open the excel files in your original question. – istrupin May 22 '17 at 15:09