Use IsEmpty on a range without a loop? XL03

[ker_01]

IsEmpty addresses a single variable; IsEmpty(MyVar) determines if it has been
intialized.

I have a named range which is 12 cells. I want to identify when the first
cell has data but the other 11 cells are 'empty'. I've simplified the code
sample below, but I realize that it will be a 2-D array and which parameter
matters will depend on whether the data is in columns or rows... I'm just
trying to see if this is even possible.

My current option is to loop:

MyAbortCode = False
for i = 2 to 12
if IsEmpty(MyArray(i))=False then MyAbortCode = True
Next

If MyAbortCode = False then
'do stuff
End If


but I'm wondering if there is any way to do this all at once without a loop.

If IsEmpty(MyArray(2 to 12))= true then
'do stuff
End If

Thank you for any ideas,
Keith

 

[Bob Phillips]

How about this?

If Application.CountA(MyArray) > 1 Then

MsgBox "More than 1"
End If

HTH

Bob

 

[Dave Peterson]

If you're looking at a contiguous range, you could use:

with worksheets("Sheet999").range("SomeNameHere")
if application.count(.cells) = 0 then
'all cells are empty
else
'not all cells are empty
end if
End with

If you wanted to see if all were filled, you could use:

with worksheets("Sheet999").range("SomeNameHere")
if application.count(.cells) = .cells.count then
'all cells are filled
else
'not all cells are filled
end if
End with

 

[ker_01]

That did help me realize that the sample range I was working with was not the
one I thought

I have formulas in some source ranges (like the one I was working with) that
throws off the IsEmpty and CountA, but .Count will be perfect (I think- I
still need to do more testing).

Thank you Bob!
Keith