Using error 1004 as a signal to take action - Excel 2010

  • I have a sheet with several blocks of data in it. My code is to simply go down the sheet from block to block and delete the blank lines between each block. (I'm preparing the data to be one big block that I can import into Access. The issue is when I get to the last block of data, the Range.End(xlDown) statement hits the bottom of the sheet and the Error 1004 Application Error is triggered. When I get to the end of the last block of data I want to end the procedure and Exit Sub. I have searched online and found an article which said I could use "On Error Resume Next" to "handle" the error but I couldn't get anything to happen other than the error message box (Error 1004 Application etc.) appearing.

    blnBottom = False
            On Error Resume Next
            ActiveCell.End(xlDown).Offset(1, 0).Select
            If Err.Number <> 0 Then
                MsgBox "End of Data Input Area", vbCritical
                Exit Sub
            End If
            Range(ActiveCell, ActiveCell.Offset(3, 0)).EntireRow.Delete

    I have shown my attempt to "handle" the error in the code snippet above. All I want to do is exit the sub when I've reached the bottom of the last block of data. I've tried a few different variations on the theme but nothing has worked. Can you help please?

  • Re: Using error 1004 as a signal to take action - Excel 2010


    You could try something like this:

  • Re: Using error 1004 as a signal to take action - Excel 2010

    Thanks for your help. I was just in the midst of editing my post because I'd made a bit of a mistake in what I had been doing and thereby posting. What I mean is, the fact that I hit the bottom of the sheet through the Range.End(xlDown) method hadn't triggered the error 1004! Instead it was my attempt to do another offset and delete the entire row when I was already at the bottom that had caused the error. I was confused that there was some type of error that occurred when hitting the bottom that I could use to signal the end of my data processing. Once I realised that I could simply use range end down to go to the bottom and I tested if the activecell.row was >100,000 then that worked fine. So all is well. Sorry to muck people around. I greatly appreciate your getting in and giving a reply to this puzzle, thanks very much. I'll leave the post as it originally stood now.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!