How to stop the Macro if Blank row Found

Question

hi I have this code below

Sub test2()
    Dim c As Range
    For Each c In Range("A1:Z50").Cells
     c.Select
        SendKeys "{F2}", True
        SendKeys "{ENTER}", True
        Next

 End Sub

Its works fine but I would like the Macros to End when it finds an Entire blank row in the range A1:Z50 . This will save a lot of my time. As it will not validate empty cell in this range.

I haven't checked but doesn't `Range("A1:Z50").Calculate` achieve the same thing? — Andy G, May 26 '14 at 12:11
possible duplicate of [Error Finding Last Used cell In VBA](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba) — RubberDuck, May 26 '14 at 12:16

score 0 · Answer 1 · answered May 26 '14 at 12:46

Give this a try:

Sub test2()
    Dim c As Range, ccheck As Range
    Dim wf As WorksheetFunction
    Set wf = Application.WorksheetFunction
    For Each c In Range("A1:Z50")
        Set ccheck = Intersect(c.EntireRow, Range("A:Z"))
        If wf.CountA(ccheck) = 0 Then Exit Sub
        c.Select
        Application.SendKeys "{F2}", True
        Application.SendKeys "{ENTER}", True
        DoEvents
    Next c
 End Sub

How to stop the Macro if Blank row Found

1 Answers1