0

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.

enter image description here

The Debugging report is here:

enter image description 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.

enter image description here

d3hero23
  • 380
  • 1
  • 12
  • 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 Jul 09 '21 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 Jul 09 '21 at 15:48
  • 1
    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 Jul 09 '21 at 15:49
  • @BigBen seemed to fix it, the script failed later on in a different sheet so now I gotta figure that one out too haha – d3hero23 Jul 09 '21 at 15:51
  • 2
    Side notes: don't use `Integer`, [use `Long`](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long). – BigBen Jul 09 '21 at 15:53
  • @BigBen it failed when using long but will try again with out the Not. If you fill out as an answer I will get yo u the solution points – d3hero23 Jul 09 '21 at 15:54
  • 1
    I'm guessing it should be `Not IsEmpty(Cells(currRow, 20))`. – BigBen Jul 09 '21 at 15:55
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/234699/discussion-between-d3hero23-and-bigben). – d3hero23 Jul 09 '21 at 16:18

1 Answers1

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:

BigBen
  • 46,229
  • 7
  • 24
  • 40