[Solved] VBA: Test range of multiple cells for empty values

  • Hi - I have a named range spanning 3 consecutive cells, and I'd like to test whether the values in the range are empty or not. If they are, then I'd like another macro to fire.


    I'm not sure of the syntax.


    Thanks
    Pete

  • Code
    1. Sub Example()
    2. Dim cell As Range
    3. For Each cell In Range("Data")
    4. If IsEmpty(cell) Then
    5. MsgBox ("Empty Cells exist") 'actions to do if true
    6. End If
    7. Next
    8. End Sub


    replace my msgbox with the code you want to run in the event of an empty cell


    Hope this helps

  • Be very very amazed - I figured it out for myself. Here's the code:


    Sub Test()
    Dim cell As Range
    Range(Range("C65536").End(xlUp).Offset(1, -2), Range("C65536").End(xlUp).Offset(1, 0)).Name = "LastRow"
    For Each cell In Range("LastRow")
    If IsEmpty(cell) Then
    GoTo 2
    Else: MsgBox ("Stuff in cells") 'actions to do if true
    Exit Sub
    End If
    2 Next
    MsgBox "Delete Row"
    End Sub


    Possibly not very elegant, but it works!


    Thanks again Will


    Pete

  • Hi guys,


    How about something like this:

    Code
    1. Sub test()
    2. If IsBlank(Range("Data")) Then MsgBox "All blank!"
    3. End Sub
    4. Function IsBlank(rng As Range) As Boolean
    5. If rng.Address = rng.SpecialCells(xlCellTypeBlanks).Address Then IsBlank = True
    6. End Function

    Not extensively tested yet as the thought has only just occurred to me (a rare event in itself! ;;) )


    HTH

  • OK, mental note - RTFQ! (Empty not Blank)


    As regards the solution, you can avoid the (slightly messy) GoTo approach by using Not. Eg:

    HTH