-3

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

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
b_ewood
  • 11
  • 5

1 Answers1

-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
user11953788
  • 14
  • 3
  • Thanks! The Microsoft.Office.Interop.Excel.dll was missing. Now i get another error. "Internal: Clound not create an instance of the code vlass because the vlass in not compiled" – b_ewood Aug 26 '19 at 13:08
  • I am not sure on how to resolve that issue. It may be because of the version of excel you are using. – user11953788 Aug 26 '19 at 13:50