Removing line break from exel with blue prism,

Question

ive got an excel-sheet with many line breaks in it. Blue Prism cant read the line breaks (and gives me an error), so i have to remove/replace them. I dont have an idea how to do it.

VBA dont work for me... dont know why. In excel it works, in the code-stage are many compiler errors.

Cells.Replace What:="" & Chr(10) & "", Replacement:=",", LookAt:=xlPart, SearchOrder _
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

Screenshot VBA

Answer 1

This is what I use to delete blank rows. You can highlight rows or a range of cells.

Sub DeleteBlankRows()

    Dim rng As Range
    Dim WorkRng As Range

    On Error Resume Next

    xTitleId = "Delete Range"
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
    xRows = WorkRng.Rows.Count
    Application.ScreenUpdating = False
    For i = xRows To 1 Step -1

    If Application.WorksheetFunction.CountA(WorkRng.Rows(i)) = 0 Then
        WorkRng.Rows(i).EntireRow.Delete XlDeleteShiftDirection.xlShiftUp
    End If
    Next

    Application.ScreenUpdating = True
End Sub