find empty cell from specific cell ranges and display result in msg box.

[ebineg]

Hi,

i have a case where i wanted to find if there is any empty cells in the specific range of cells. if the macro find any empty cell it should show the cell names in message box or the empty cell can be highlighted, if there is no empty cell then"NO EMPTY CELLS FOUND" should be displayed in msg box.Could anyone help me with this vba code?

My selective ranges are

G13:G32
G35:G54
G57:G76
G79:G98

Thanks in advance.

 

[Joe4]

Try this:

Sub FindBlanks()
    
    Dim myRange As Range
    Dim cell As Range
    Dim myString As String
    
'   Set range to check
    Set myRange = Range("G13:G32, G35:G54, G57:G76, G79:G98")
    
'   Check all cells
    For Each cell In myRange
        If cell = "" Then
            myString = myString & cell.Address(0, 0) & ","
        End If
    Next cell
    
'   Return message box
    If Len(myString) = 0 Then
        MsgBox "NO EMPTY CELLS FOUND"
    Else
        MsgBox "Empty cells found in following cells:" & vbCrLf & Left(myString, Len(myString) - 1)
    End If
    
End Sub

 

[Joe4]

Note. A simple non-VBA solution that may work for you is to simply use Conditional Formatting to highlight the blank cells in your designated ranges.
The main advantage there is it is automatic and you do not need to run anything.

 

[ebineg]

Thanks it worked joe

 

[Joe4]

You are welcome!