Is Worksheet Empty?
 

I found that if a worksheet is completely empty, its UsedRange will be $A$1.
Then if A1 is empty, the worksheet is blank. But it seems there should be a
more elegant way to determine whether the worksheet is blank. TIA, James

Is Worksheet Empty?
 

Sub zone()
MsgBox (Application.WorksheetFunction.CountA(Cells))
End Sub

will report 0 on an empty sheet
--
Gary''s Student - gsnu200745


"Zone" wrote:

I found that if a worksheet is completely empty, its UsedRange will be $A$1.
Then if A1 is empty, the worksheet is blank. But it seems there should be a
more elegant way to determine whether the worksheet is blank. TIA, James

Is Worksheet Empty?
 

Cool, Thanks! A question. It seems to work just as well like this.
Sub zone()
MsgBox WorksheetFunction.CountA(Cells)
End Sub
Any problem with this? James

"Gary''s Student" wrote in message
...
Sub zone()
MsgBox (Application.WorksheetFunction.CountA(Cells))
End Sub

will report 0 on an empty sheet
--
Gary''s Student - gsnu200745


"Zone" wrote:

I found that if a worksheet is completely empty, its UsedRange will be
$A$1.
Then if A1 is empty, the worksheet is blank. But it seems there should
be a
more elegant way to determine whether the worksheet is blank. TIA, James

Is Worksheet Empty?
 

The only problem is :

What does Empty mean??

If a worksheet has many inserted comments and nothing else, COUNTA will
ignore them, but Activesheet.Usedrange will not.

If a worksheet has many cells with the background color set and nothing
else, COUNTA will ignore them, but Activesheet.Usedrange will not.

If a worksheet has ClipArt and nothing else, both COUNTA and
Activesheet.UsedRange will tell you nothing is there!
--
Gary''s Student - gsnu200745


"Zone" wrote:

Cool, Thanks! A question. It seems to work just as well like this.
Sub zone()
MsgBox WorksheetFunction.CountA(Cells)
End Sub
Any problem with this? James

"Gary''s Student" wrote in message
...
Sub zone()
MsgBox (Application.WorksheetFunction.CountA(Cells))
End Sub

will report 0 on an empty sheet
--
Gary''s Student - gsnu200745


"Zone" wrote:

I found that if a worksheet is completely empty, its UsedRange will be
$A$1.
Then if A1 is empty, the worksheet is blank. But it seems there should
be a
more elegant way to determine whether the worksheet is blank. TIA, James

Is Worksheet Empty?
 

Yes, I understand what you mean. Concise wording of questions is so
important, yet so rare! Let's say I want to determine only whether the
worksheet contains data in the cells. Then your COUNTA is ideal. What I
really meant by my follow-up question is that I changed your code, leaving
out Application. and the parentheses around the MsgBox's argument. I have a
mania for compacting instructions into the shortest form possible. Do you
see any problem with leaving out Application. and the extra parentheses?
James

"Gary''s Student" wrote in message
...
The only problem is :

What does Empty mean??

If a worksheet has many inserted comments and nothing else, COUNTA will
ignore them, but Activesheet.Usedrange will not.

If a worksheet has many cells with the background color set and nothing
else, COUNTA will ignore them, but Activesheet.Usedrange will not.

If a worksheet has ClipArt and nothing else, both COUNTA and
Activesheet.UsedRange will tell you nothing is there!
--
Gary''s Student - gsnu200745


"Zone" wrote:

Cool, Thanks! A question. It seems to work just as well like this.
Sub zone()
MsgBox WorksheetFunction.CountA(Cells)
End Sub
Any problem with this? James

"Gary''s Student" wrote in
message
...
Sub zone()
MsgBox (Application.WorksheetFunction.CountA(Cells))
End Sub

will report 0 on an empty sheet
--
Gary''s Student - gsnu200745


"Zone" wrote:

I found that if a worksheet is completely empty, its UsedRange will be
$A$1.
Then if A1 is empty, the worksheet is blank. But it seems there
should
be a
more elegant way to determine whether the worksheet is blank. TIA,
James

Is Worksheet Empty?
 

Because "CountA" is a worksheet function, you must use "Application" in
front of it. But you can delete the "WorksheetFunction". HTH Otto
"Zone" wrote in message
...
Yes, I understand what you mean. Concise wording of questions is so
important, yet so rare! Let's say I want to determine only whether the
worksheet contains data in the cells. Then your COUNTA is ideal. What I
really meant by my follow-up question is that I changed your code, leaving
out Application. and the parentheses around the MsgBox's argument. I have
a mania for compacting instructions into the shortest form possible. Do
you see any problem with leaving out Application. and the extra
parentheses? James

"Gary''s Student" wrote in
message ...
The only problem is :

What does Empty mean??

If a worksheet has many inserted comments and nothing else, COUNTA will
ignore them, but Activesheet.Usedrange will not.

If a worksheet has many cells with the background color set and nothing
else, COUNTA will ignore them, but Activesheet.Usedrange will not.

If a worksheet has ClipArt and nothing else, both COUNTA and
Activesheet.UsedRange will tell you nothing is there!
--
Gary''s Student - gsnu200745


"Zone" wrote:

Cool, Thanks! A question. It seems to work just as well like this.
Sub zone()
MsgBox WorksheetFunction.CountA(Cells)
End Sub
Any problem with this? James

"Gary''s Student" wrote in
message
...
Sub zone()
MsgBox (Application.WorksheetFunction.CountA(Cells))
End Sub

will report 0 on an empty sheet
--
Gary''s Student - gsnu200745


"Zone" wrote:

I found that if a worksheet is completely empty, its UsedRange will
be
$A$1.
Then if A1 is empty, the worksheet is blank. But it seems there
should
be a
more elegant way to determine whether the worksheet is blank. TIA,
James

Is Worksheet Empty?
 

Thank you for your reply, Otto. It's helpful because it shows me that the
instruction may be shortened to Application.CountIf, which I didn't realize.
It is a source of curiosity to me, though, that either
WorksheetFunction.CountIf or Application.CountIf will work, but CountIf will
not work without either one of the qualifiers. With my mania for shortening
statements to their most compact form, I prefer Application.CountIf over
WorksheetFunction.CountIf because the former is shorter than the latter.
But your comment indicates a preference for including the Application
qualifier over the WorksheetFunction qualifier as a matter of form. Would
you please expound on this a bit? Regards, James

"Otto Moehrbach" wrote in message
...
Because "CountA" is a worksheet function, you must use "Application" in
front of it. But you can delete the "WorksheetFunction". HTH Otto
"Zone" wrote in message
...
Yes, I understand what you mean. Concise wording of questions is so
important, yet so rare! Let's say I want to determine only whether the
worksheet contains data in the cells. Then your COUNTA is ideal. What I
really meant by my follow-up question is that I changed your code,
leaving out Application. and the parentheses around the MsgBox's
argument. I have a mania for compacting instructions into the shortest
form possible. Do you see any problem with leaving out Application. and
the extra parentheses? James

"Gary''s Student" wrote in
message ...
The only problem is :

What does Empty mean??

If a worksheet has many inserted comments and nothing else, COUNTA will
ignore them, but Activesheet.Usedrange will not.

If a worksheet has many cells with the background color set and nothing
else, COUNTA will ignore them, but Activesheet.Usedrange will not.

If a worksheet has ClipArt and nothing else, both COUNTA and
Activesheet.UsedRange will tell you nothing is there!
--
Gary''s Student - gsnu200745


"Zone" wrote:

Cool, Thanks! A question. It seems to work just as well like this.
Sub zone()
MsgBox WorksheetFunction.CountA(Cells)
End Sub
Any problem with this? James

"Gary''s Student" wrote in
message
...
Sub zone()
MsgBox (Application.WorksheetFunction.CountA(Cells))
End Sub

will report 0 on an empty sheet
--
Gary''s Student - gsnu200745


"Zone" wrote:

I found that if a worksheet is completely empty, its UsedRange will
be
$A$1.
Then if A1 is empty, the worksheet is blank. But it seems there
should
be a
more elegant way to determine whether the worksheet is blank. TIA,
James

Is Worksheet Empty?
 

Zone
To tell you the truth, I didn't know that it would work with just the
WorksheetFunction. It used to be that you had to have both that and
Application. Then I found out, in later versions, that the
WorksheetFunction was not necessary. But you have to have one or the other
or both because if you had neither, Excel would be looking for a VBA
function of CountA and there is no such function in VBA. HTH Otto
"Zone" wrote in message
...
Thank you for your reply, Otto. It's helpful because it shows me that the
instruction may be shortened to Application.CountIf, which I didn't
realize. It is a source of curiosity to me, though, that either
WorksheetFunction.CountIf or Application.CountIf will work, but CountIf
will not work without either one of the qualifiers. With my mania for
shortening statements to their most compact form, I prefer
Application.CountIf over WorksheetFunction.CountIf because the former is
shorter than the latter. But your comment indicates a preference for
including the Application qualifier over the WorksheetFunction qualifier
as a matter of form. Would you please expound on this a bit? Regards,
James

"Otto Moehrbach" wrote in message
...
Because "CountA" is a worksheet function, you must use "Application" in
front of it. But you can delete the "WorksheetFunction". HTH Otto
"Zone" wrote in message
...
Yes, I understand what you mean. Concise wording of questions is so
important, yet so rare! Let's say I want to determine only whether the
worksheet contains data in the cells. Then your COUNTA is ideal. What
I really meant by my follow-up question is that I changed your code,
leaving out Application. and the parentheses around the MsgBox's
argument. I have a mania for compacting instructions into the shortest
form possible. Do you see any problem with leaving out Application. and
the extra parentheses? James

"Gary''s Student" wrote in
message ...
The only problem is :

What does Empty mean??

If a worksheet has many inserted comments and nothing else, COUNTA will
ignore them, but Activesheet.Usedrange will not.

If a worksheet has many cells with the background color set and nothing
else, COUNTA will ignore them, but Activesheet.Usedrange will not.

If a worksheet has ClipArt and nothing else, both COUNTA and
Activesheet.UsedRange will tell you nothing is there!
--
Gary''s Student - gsnu200745


"Zone" wrote:

Cool, Thanks! A question. It seems to work just as well like this.
Sub zone()
MsgBox WorksheetFunction.CountA(Cells)
End Sub
Any problem with this? James

"Gary''s Student" wrote in
message
...
Sub zone()
MsgBox (Application.WorksheetFunction.CountA(Cells))
End Sub

will report 0 on an empty sheet
--
Gary''s Student - gsnu200745


"Zone" wrote:

I found that if a worksheet is completely empty, its UsedRange will
be
$A$1.
Then if A1 is empty, the worksheet is blank. But it seems there
should
be a
more elegant way to determine whether the worksheet is blank. TIA,
James

Is Worksheet Empty?
 

Otto, Thank you for teaching me the WorksheetFunction part could be
eliminated. James

"Otto Moehrbach" wrote in message
...
Zone
To tell you the truth, I didn't know that it would work with just the
WorksheetFunction. It used to be that you had to have both that and
Application. Then I found out, in later versions, that the
WorksheetFunction was not necessary. But you have to have one or the
other or both because if you had neither, Excel would be looking for a VBA
function of CountA and there is no such function in VBA. HTH Otto
"Zone" wrote in message
...
Thank you for your reply, Otto. It's helpful because it shows me that
the instruction may be shortened to Application.CountIf, which I didn't
realize. It is a source of curiosity to me, though, that either
WorksheetFunction.CountIf or Application.CountIf will work, but CountIf
will not work without either one of the qualifiers. With my mania for
shortening statements to their most compact form, I prefer
Application.CountIf over WorksheetFunction.CountIf because the former is
shorter than the latter. But your comment indicates a preference for
including the Application qualifier over the WorksheetFunction qualifier
as a matter of form. Would you please expound on this a bit? Regards,
James

"Otto Moehrbach" wrote in message
...
Because "CountA" is a worksheet function, you must use "Application" in
front of it. But you can delete the "WorksheetFunction". HTH Otto
"Zone" wrote in message
...
Yes, I understand what you mean. Concise wording of questions is so
important, yet so rare! Let's say I want to determine only whether the
worksheet contains data in the cells. Then your COUNTA is ideal. What
I really meant by my follow-up question is that I changed your code,
leaving out Application. and the parentheses around the MsgBox's
argument. I have a mania for compacting instructions into the shortest
form possible. Do you see any problem with leaving out Application.
and the extra parentheses? James

"Gary''s Student" wrote in
message ...
The only problem is :

What does Empty mean??

If a worksheet has many inserted comments and nothing else, COUNTA
will
ignore them, but Activesheet.Usedrange will not.

If a worksheet has many cells with the background color set and
nothing
else, COUNTA will ignore them, but Activesheet.Usedrange will not.

If a worksheet has ClipArt and nothing else, both COUNTA and
Activesheet.UsedRange will tell you nothing is there!
--
Gary''s Student - gsnu200745


"Zone" wrote:

Cool, Thanks! A question. It seems to work just as well like this.
Sub zone()
MsgBox WorksheetFunction.CountA(Cells)
End Sub
Any problem with this? James

"Gary''s Student" wrote in
message
...
Sub zone()
MsgBox (Application.WorksheetFunction.CountA(Cells))
End Sub

will report 0 on an empty sheet
--
Gary''s Student - gsnu200745


"Zone" wrote:

I found that if a worksheet is completely empty, its UsedRange
will be
$A$1.
Then if A1 is empty, the worksheet is blank. But it seems there
should
be a
more elegant way to determine whether the worksheet is blank.
TIA, James

Is Worksheet Empty?
 

I've also noticed that if the value being looked up does not exist in the
table, the WorksheetFunction will error out (Run Time Error 1004- cannot get
the Vlookup property of the WorksheetFunction class") even if I use a variant
to contain the return value of vlookup. This doesn't seem to happen with
using Application.Vlookup - the error is stored in the variant variable and
the code continues on. I've never come across an explanation.

Sub test()
Dim rngData As Range
Dim varResult As Variant

Set rngData = Sheet1.Range("A1:B5")
varResult = Application.VLookup("Test", rngData, 2, 0)
varResult = WorksheetFunction.VLookup("Test", rngData, 2, 0)
varResult = Application.WorksheetFunction.VLookup("Test", rngData, 2, 0)
End Sub


"Otto Moehrbach" wrote:

Zone
To tell you the truth, I didn't know that it would work with just the
WorksheetFunction. It used to be that you had to have both that and
Application. Then I found out, in later versions, that the
WorksheetFunction was not necessary. But you have to have one or the other
or both because if you had neither, Excel would be looking for a VBA
function of CountA and there is no such function in VBA. HTH Otto
"Zone" wrote in message
...
Thank you for your reply, Otto. It's helpful because it shows me that the
instruction may be shortened to Application.CountIf, which I didn't
realize. It is a source of curiosity to me, though, that either
WorksheetFunction.CountIf or Application.CountIf will work, but CountIf
will not work without either one of the qualifiers. With my mania for
shortening statements to their most compact form, I prefer
Application.CountIf over WorksheetFunction.CountIf because the former is
shorter than the latter. But your comment indicates a preference for
including the Application qualifier over the WorksheetFunction qualifier
as a matter of form. Would you please expound on this a bit? Regards,
James

"Otto Moehrbach" wrote in message
...
Because "CountA" is a worksheet function, you must use "Application" in
front of it. But you can delete the "WorksheetFunction". HTH Otto
"Zone" wrote in message
...
Yes, I understand what you mean. Concise wording of questions is so
important, yet so rare! Let's say I want to determine only whether the
worksheet contains data in the cells. Then your COUNTA is ideal. What
I really meant by my follow-up question is that I changed your code,
leaving out Application. and the parentheses around the MsgBox's
argument. I have a mania for compacting instructions into the shortest
form possible. Do you see any problem with leaving out Application. and
the extra parentheses? James

"Gary''s Student" wrote in
message ...
The only problem is :

What does Empty mean??

If a worksheet has many inserted comments and nothing else, COUNTA will
ignore them, but Activesheet.Usedrange will not.

If a worksheet has many cells with the background color set and nothing
else, COUNTA will ignore them, but Activesheet.Usedrange will not.

If a worksheet has ClipArt and nothing else, both COUNTA and
Activesheet.UsedRange will tell you nothing is there!
--
Gary''s Student - gsnu200745


"Zone" wrote:

Cool, Thanks! A question. It seems to work just as well like this.
Sub zone()
MsgBox WorksheetFunction.CountA(Cells)
End Sub
Any problem with this? James

"Gary''s Student" wrote in
message
...
Sub zone()
MsgBox (Application.WorksheetFunction.CountA(Cells))
End Sub

will report 0 on an empty sheet
--
Gary''s Student - gsnu200745


"Zone" wrote:

I found that if a worksheet is completely empty, its UsedRange will
be
$A$1.
Then if A1 is empty, the worksheet is blank. But it seems there
should
be a
more elegant way to determine whether the worksheet is blank. TIA,
James