1

I have the following code which I keep getting runtime error 1004. How or what can you suggest to stop error at the ActiveCell.Offset(1, 0).Select in the below code:

Sub RowCounter (count)

Counter = 1
Do Until ActiveCell = ""
Counter = counter + 1
ActiveCell.Offset(1, 0).Select
Loop

Count = Counter > 2000000
Georgette Morris
  • 11
  • 1
  • 1
    I'm sorry, but this code doesn't make any sense without knowing your goal with the code. – Luuklag Sep 10 '18 at 12:19
  • ^^As per @Luuklag Please state what your intended outcome is... Also, how you are calling this. And it is really expensive to use .Select operation and bug prone to work with implicit activesheet references (i.e. not specifying the sheet to work with.) – QHarr Sep 10 '18 at 12:21
  • 1
    @QHarr, OP stated at which line: `ActiveCell.Offset(1, 0).Select` To debug further it would be good to know at which `ActiveCell` the code starts, and what the value of `Counter` is at the moment the code errors out. – Luuklag Sep 10 '18 at 12:24
  • Unless they are at the bottom of the sheet, or no sheet in focus, I am unsure how they could generate that error. Point noted though - consider my wrist sternly slapped. – QHarr Sep 10 '18 at 12:25
  • As to the purpose of this `Sub` (...function?): It seems to evaluate whether any of the first two million cells in the current column is empty. The loop stops upon encountering an empty cell and the Sub/function then returns a value via the implicitly `ByRef` `count`, of the implicit type `Variant/Boolean`: `True` for no empty cells, `False` for at least one empty cell. – Inarion Sep 10 '18 at 12:29
  • To add to my previous comment (as I misread that algorithm a bit): It checks the first 2M cells below the `ActiveCell` and doesn't start at row 1. – Inarion Sep 10 '18 at 13:07

1 Answers1

-1

The code counts how many cells with value are below the activecell. Try this to avoid the error, showing the answer in a MsgBox():

Sub RowCounter()

    Dim counter As Long
    counter = 1
    Do Until ActiveCell = "" Or ActiveCell.Row <> Rows
        counter = counter + 1
        ActiveCell.Offset(1, 0).Select
    Loop

    MsgBox counter

End Sub

The Or ActiveCell.Row <> Rows makes sure to exit, if the ActiveCell is on the last row of the Excel spreadsheet.

Concerning the .Select and ActiveCell part, see this topic with some ideas how to improve it:

Vityata
  • 42,633
  • 8
  • 55
  • 100
  • 1
    Huh, you're right. My comment below the question is thus incorrect. (I assumed the first 2M cells were checked, but it's the first 2M below `ActiveCell`...) – Inarion Sep 10 '18 at 13:06