CODEWITHSUNDEEP
excelvba
rob_is_great
rob_is_great

Reputation: 3

How can I get this VBA code to loop through worksheets in a specified location and remove the carriage returns within every cell of each?

So I have a need to efficiently remove carriage returns within a CSV for feeding into a separate piece of software that would get tripped up by the line breaks. The trouble with these CSVs is that the line breaks within the cells could be anywhere, and they are many thousands of columns and rows.

I found some code that will successfully remove the carriage returns by looping through all the cells and replacing all of the carriage returns (character 10 is what it is designated in excel) with a space instead. I had tried editing the active workbook part to designate to workbooks/sheets in a location, but it kicked out an error. I am providing the code that works to remove the returns below. I just need it now to do it in some specified workbook and possibly through selection of a file dialog box without opening said workbook.

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

Upvotes: 0

Views: 82

Answers (1)

Damian
Damian

Reputation: 5174

Along with BigBen's comment this should help you:

Option Explicit
Sub RemoveCarriageReturns()

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Dim wb As Workbook 'declare a workbook type variable
    Dim MyFile As String 'declare a string variable to hold the file you want to pick
    Dim fd As FileDialog 'declare a file dialog variable

    Set fd = Application.FileDialog(msoFileDialogFilePicker)

    With fd
        .AllowMultiSelect = False
        .Title = "Please select a file"
        .Filters.Clear
        .Filters.Add "*", "*.csv"  'filter only csv files
        If .Show = True Then
            MyFile = .SelectedItems(1)
        Else
            MsgBox "you didn't pick a file, please start over."
            End
        End If
    End With

    Set wb = Workbooks.Open(MyFile) 'set the workbook variable openning the file you picked

    With wb.Sheets(1) 'assuming you have only 1 sheet per file, if not change 1 for "SheetName"
        .UsedRange.Replace Chr(10), ""
    End With

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

End Sub

Upvotes: 2

Related Questions