Quickly determining whether any values in a range are empty

[ric_deez]

Hi there,

I am trying to find a way of quickly determining whether any values in
a range are empty, preferably without scanning the contents of the
cells individually.

For instance, it would be ideal to have something like
Sheet1.range("A1:A10").value = NullArray where NullArray is an array of
Null values of the same length as the number of cells being checked.

1. Is this possible?
2. If not, can someone suggest a better way, say using VBA with a range
formula or similar method?

I was trying to avoid the expensive alternative of doing something
like:

NullValue = False
numRows = Sheet1.Range("A1:A10").End(xlUp).Row
for i in 1 to numRows
if len(Sheet1.range("A" & i).value) = 0 then
NullValue = True
end if
next i


Any assistance would be appreciated.

Regards,

Ric

 

[Norman Jones]

Hi Ric,

Try something like:

Dim rng As Range

Set rng = Range("A1:A10")

If Application.CountA(rng) < rng.Count Then
MsgBox "Empty cells present in range"
End If

 

[ric_deez]

Hi Norman,

That was concise and clever!!! Thank for your prompt response...

Ric