Checking for non-blank cells in named range

[Guest]

I have a range, let's call it myRange that's cells B22:B24 in my worksheet.
I need to check that these cells are all not blank before doing something
else. I'm not sure how to write the code to check that all are non-blank.
Any suggestions?

Thanks

 

[Guest]

Here is a simple function that will tell you if a range of cells contains any
blanks.

Public Function HasBlanks(ByVal rng As Range) As Boolean
Dim rngBlanks As Range

On Error Resume Next
Set rngBlanks = rng.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If rngBlanks Is Nothing Then
HasBlanks = False
Else
HasBlanks = True
End If
End Function

You can use it like this.
if hasblanks(sheet1.Range("B22:B24")) = true then
msgbox "you need to fill some stuff in!"
else
'do some stuff
end if

 

[Tom Ogilvy]

if application.CountA(Range("B22:B24")) = 3 then
' all filled
else
msgbox Application.CountBlank(Range("B22:B24")) & " blank cells"
end if

Replae "B22:B24" with myRange if it is a range variable or

Range("myrange") if it is a named range.

if the range is variable, you could do

if application.CountA(Range("B22:B24")) = Range("B22:B24").Count then


or use CountBlank in the original test and check for a value of zero.