0

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

Robby
  • 843
  • 3
  • 19
  • 53
  • What is the actual error message? I don't see how that line of code could throw an overflow error. Perhaps there is some other code in your workbook, triggered by an event handler, which is causing the actual error. – John Coleman Jul 30 '19 at 14:48
  • The `InStr` function takes a string, not a range. It's implicitely converting your range to the string contained in it, but it's not good to rely on that. You'll get better error results if you make your code more explicit. For example, replace `For Each MyRange In ActiveSheet.UsedRange` with `For Each MyRange In ActiveSheet.UsedRange.Cells`, and replace `InStr(MyRange, Chr(10))` with `InStr(MyRange.Text, Chr(10))`. – Josh Eller Jul 30 '19 at 14:54
  • @JoshEller I'd avoid using `.Text` for this. `.Text` returns whatever is displayed on the screen so you could end up with truncated results. You'd be better using `.Value2` [What is the difference between .text, .value, and .value2?](https://stackoverflow.com/a/17363466/3042759) – Tom Jul 30 '19 at 14:58
  • My guess is that it is `Replace(MyRange, Chr(10), "")` which is causing the overflow error (in a way that I can't reproduce) caused by Excel trying to (in some cases) interpret the result of removing the line breaks as a numerical expression. What are the contents of the cell that triggers the error? – John Coleman Jul 30 '19 at 15:13
  • @JohnColeman How can I tell which cell is triggering the error? – Robby Jul 30 '19 at 15:18
  • One way is to put `Debug.Print MyRange.Address` as the first line of the loop. What is the last address printed? What were the contents? Without a [mcve] it is hard to see what is happening. – John Coleman Jul 30 '19 at 15:25
  • I get the same error: Run-time error '6': Overflow – Robby Jul 30 '19 at 15:52
  • 1
    Something should have been printed to the immediate window before you encountered the bug. You can also investigate `MyRange` in the Locals window when you hit the bug. You can also step through the code while in debug mode. See [this](https://analystcave.com/how-to-debug-vba/) for some tips on VBA debugging. – John Coleman Jul 30 '19 at 16:08
  • I've never used the Locals window before, so I had to figure out what I was doing. But using that, I was able to figure out which cell it was getting hung up on. The problem was a cell with a 7 digit number that was formatted as a Date, for some reason. After I changed it to General, it worked. – Robby Jul 30 '19 at 18:13

1 Answers1

2

You could do the same with

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

No need for looping

Tom
  • 9,725
  • 3
  • 31
  • 48