In Testing for Empty Cells, I described the IsEmpty function and how it will tell you if a cell is truly empty. If you need to determine if a whole range is empty, IsEmpty won’t do the trick. IsEmpty returns TRUE if the variable passed to it is uninitialized. The Value property of a Range object meets that criterion, but the Value property of a multi-cell Range object is an array. Arrays are never empty (I think) even when there’s nothing in them. Presumably this is because they are initialized when they are created. I really don’t have a complete understanding of this whole process, but I do know that IsEmpty doesn’t work.
There has to be something that works, though. I set up this procedure and looked at the Locals Window in the VBE.
Dim rRng As Range
Set rRng = Range(“B8:B11”)
I got as far as the F’s when I found the holy grail. It seems that the FormulaArray property provides the information I want. If the range actually contains an array formula, then
Len(rRng.FormulaArray) will return a non-zero number. If the range contains some data, that code will return Null. If the range is empty, the return value is zero.
If IsNull(rRng.FormulaArray) Then
RangeIsBlank = False
RangeIsBlank = Len(rRng.FormulaArray) = 0