validate range of cells

[Jmacp]

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

?

[DKenny]

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

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

[Webtest]

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.

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!