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

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

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.

--
Regards,
Tom Ogilvy


"Barb Reinhardt" wrote in message
...
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