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
?
Printable View
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.Good Luck and Good Programming!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