validate range of cells

how do i , in excel, validate that a range of cells are not empty?

like,

for each cells in cells(A4:c5)
if not cells(i) = "" then

?

Here's a function that will perform this check, it returns TRUE if all cells in the range have a value.

VB Code:

Function ValidateRange(CheckCells As Range) As Boolean
Dim rngCell As Range
    
    'Assume all cells have values
    ValidateRange = True
    
    For Each rngCell In CheckCells
        
        'if a blank is found...
        If rngCell.Value = "" Then
            
            '...return FALSE
            ValidateRange = False
            '...and discontinue checking
            Exit For
            
        End If
        
    Next rngCell
    
End Function

Here is an alternative using the Worksheet "COUNTA" function:

COUNTA

Counts the number of cells that are not empty and the values within the list of arguments. Use COUNTA to count the number of cells that contain data in a range or array.

Code:

---

Option Explicit
Sub CheckRangeForEmptyCells()
    'This routine ignores any and all cell formatting.
    Dim aRange As Range

    'Set the range here however you want.
    'I'm just using "Selection" as a test example
    Set aRange = Selection
   
    'Here is a prototype test ... you can also just use the CountA function
    If Application.CountA(aRange) = aRange.Cells.Count Then
        'There are NO empty cells here ... all cells are occupied
        MsgBox "All Selected cells are occupied!"
    Else
        'There is at least 1 empty cell in the defined range
        MsgBox "There are some empty cells in the selected range."
    End If

    'Clean House
    Set aRange = Nothing

End Sub

---

Good Luck and Good Programming!