Msgbox all empty rows in a range

Originally Posted by nickmax1

Olly - that works great, but only if there are blank rows to find. - how do you change it so that it doesnt crash if there are NO blank cells?

Adding a bit of error handling:

Sub foo()
    Dim r As Range
    On Error GoTo NoBlanks
    Set r = Range("B18:B" & Range("B191").End(xlUp).Row).SpecialCells(xlCellTypeBlanks)
    MsgBox "Blank cells in " & Replace(r.Address(False, False), "B", ""), vbInformation + vbOKOnly, "Blanks"
    Exit Sub
NoBlanks:
    MsgBox "No blank cells found", vbInformation + vbOKOnly, "Blanks"
End Sub

Also tweaked to return just the rows, not the full cell addresses.