Checking for non-blank cells in named range
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
--
HTH...
Jim Thomlinson
"Barb Reinhardt" wrote:
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
|