isempty
 

very simple question, but i am stuck. i wish to see if a range is empty.
that's it.

If IsEmpty(Range("a16:a31")) Then
msgbox "all cells are empty"
else
msgbox "at least one of the cells are NOT empty"
end if

this doesn't work for some reason, even though every cell from a16:a31 are
clearly blank. it comes back as false, thus the latter statement pops up.
help. thanks, mike allen

isempty
 

isempty works on a single cell

If worksheetfunction.countA(Range("a16:a31")) = 0 Then
msgbox "all cells are empty"
else
msgbox "at least one of the cells are NOT empty"
end if

--
Regards,
Tom Ogilvy

mike allen wrote in message
...
very simple question, but i am stuck. i wish to see if a range is empty.
that's it.

If IsEmpty(Range("a16:a31")) Then
msgbox "all cells are empty"
else
msgbox "at least one of the cells are NOT empty"
end if

this doesn't work for some reason, even though every cell from a16:a31 are
clearly blank. it comes back as false, thus the latter statement pops up.
help. thanks, mike allen

isempty
 

Mike,

IsEmpty will always return False if you use a multi-cell range, regardless
of what is in those cells (or not).

Try using the worksheet COUNTA function, circa

If WorksheetFunction.CountA(Range("A1:6:A31")) = 0 Then
MsgBox "all cells are empty"
End If

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"mike allen" wrote in message
...
very simple question, but i am stuck. i wish to see if a range is empty.
that's it.

If IsEmpty(Range("a16:a31")) Then
msgbox "all cells are empty"
else
msgbox "at least one of the cells are NOT empty"
end if

this doesn't work for some reason, even though every cell from a16:a31 are
clearly blank. it comes back as false, thus the latter statement pops up.
help. thanks, mike allen