Question:

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

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:

<ul><li><a href="https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba" rel="nofollow">How to avoid using Select in Excel VBA</a></li> </ul>