• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find next empty cell giving error message.

Frncis

Member
I am trying find the next empty cell in Column B. When the code below is run it provides this error, "Rum time error 1004; Application-defined or object defined error".
Code:
Private Sub Worksheet_Activate()
Range("B7").End(xlDown).Offset(2, 0).Select
End Sub
B6 has text, but is locked. I have tried to figure out what I am doing wrong, but have not been successful. Am i correct in thinking that this line Range("B7").End(xlDown).Offset(2, 0).Select, tells it to go to B7 & then go down to the next empty one.
 
If B8 & below are all empty cells it will take you to B1048576 (ie very last row) & then it tries to move 2cells down hence the error.
Try
Code:
Range("B" & Rows.Count).End(xlUp).Offset(2).Select
 
If B8 & below are all empty cells it will take you to B1048576 (ie very last row) & then it tries to move 2cells down hence the error.
Try
Code:
Range("B" & Rows.Count).End(xlUp).Offset(2).Select
Thank you for being so prompt & explaining why i got the error. I didn't think about B1048576 (ie very last row) . I knew about it, but i didn't register. I guess, I need more coffee.
 
Back
Top