A simple way to check if range has no value

Hi Experts,

I have a worksheet that where, in Range B1:B5, I have formulas that can evaluate to numbers or nothing as in "" e.g. =IF(A5,5,""). I could loop through the range and check if all cells contain no values and exit the sub,

However, is there a way I can check the range itself? I can't find any member of the range object that will allow me to test for emptyness.

Thanks,

OS
onesegunAsked:
Who is Participating?
 
TracyConnect With a Mentor VBA DeveloperCommented:
So to expand, you could say

=If(CountA(B1:B5)=5,"All blank","Some Values")
0
 
TracyVBA DeveloperCommented:
CountA will count blank (empty) cells, even if there's a formula but the result ends up being "", this will be counted as blank.
0
 
patrickabConnect With a Mentor Commented:
onesegun,

Counta() will count the number of cells that are not empty. They can contain a value or a formula for Counta() to count them.

However Count() will only count cells that have a value. So if you want to know how many cells in a range do not have a value you could use:

=Counta(B1:B5)-Count(B1:B5)

Patrick
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
TracyVBA DeveloperCommented:
Yes, Patrick, that's what I did, but instead of counting the non blanks as well and subtracting, I just set it to equal 5, since the range was small.  But for longer ranges, I agree, with your method.
0
 
onesegunAuthor Commented:
Hi All,

Thanks for your help. But what I found which helped me was

If WorksheetFunction.CountBlank.Range("B1:B5")

Open in new window

....

First of all I needed this in a vba module and not a worksheet and secondly the CountBlank property seemed to work just fine. So I can test if all cells are blank if the number is equal to 5.

Thanks,

OS
0
 
onesegunAuthor Commented:
Countblank is a more direct solution
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.