CODEWITHSUNDEEP
excelvba
Robby
Robby

Reputation: 827

Removing linebreaks in Excel via VBA results in overflow error

In a particular workbook, when I use this code to remove linebreaks, I get an overflow error. More specifically, it's hung up on the If 0 < InStr(MyRange, Chr(10)) Then bit. There isn't much data on the worksheet at all. I've definitely successfully ran the code on much larger sets of data. What could be causing this?

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

Edit: The error I'm getting is this: Run-time error '6': Overflow

Upvotes: 0

Views: 202

Answers (1)

Tom
Tom

Reputation: 9878

You could do the same with

ActiveSheet.UsedRange.Replace what:=Chr(10), replacement:=vbNullString

No need for looping

Upvotes: 3

Related Questions