VBA Error Run-time error 6 'Overflow', What is the problem?

Question

I am a Programmer but never use VBA. I am taking over someone's old script and it keeps failing at a certain point with a Run-Time Error.

The Debugging report is here:

Please let me know if you need more code. I have tried to change currRow to Long, single, double, string none work. Below is a quick glimpse of the sheet that is failing. The top row is row one.

What are you trying to do with Not Cells(currRow, 20)? Note: screenshots of code aren't allowed, please edit your question with the code as text. — BigBen
– BigBen, Commented Jul 9, 2021 at 15:45
@BigBen not sure, I inherited this script. I am not even sure what the index is referencing (currRow, 20) = (row, column)? — d3hero23
– d3hero23, Commented Jul 9, 2021 at 15:48
Yes, it's row, column, but the real problem is Not. Any chance it's missing an IsEmpty? Probably this is a poor attempt to find the last row. — BigBen
– BigBen, Commented Jul 9, 2021 at 15:49
I'm guessing it should be Not IsEmpty(Cells(currRow, 20)). — BigBen
– BigBen, Commented Jul 9, 2021 at 15:55

BigBen · Accepted Answer · 2021-07-09 16:11:30Z

3

My guess is that this is missing an IsEmpty:

Do While Not IsEmpty(Cells(currRow, 20))

However, this loop is a very inefficient way to find the last row.

I believe something like this is what you want:

With ActiveWorkbook.Worksheets("Analysis")
    Dim lastRow As Long
    lastRow = .Cells(.Rows.Count, 20).End(xlUp).Row

    .Range(lastRow + 1 & ":" & .Rows.Count).ClearContents
End With

Additionally:

edited Jul 9, 2021 at 16:11

answered Jul 9, 2021 at 15:59

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA Error Run-time error 6 'Overflow', What is the problem?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related