Use IsEmpty on a range without a loop? XL03
 

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

Use IsEmpty on a range without a loop? XL03
 

How about this?

If Application.CountA(MyArray) 1 Then

MsgBox "More than 1"
End If

HTH

Bob


"ker_01" wrote in message
...
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

Use IsEmpty on a range without a loop? XL03
 

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 wrote:

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

--

Dave Peterson

Use IsEmpty on a range without a loop? XL03
 

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

"Bob Phillips" wrote:

How about this?

If Application.CountA(MyArray) 1 Then

MsgBox "More than 1"
End If

HTH

Bob


"ker_01" wrote in message
...
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


.