Is Worksheet Empty?

[Zone]

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

 

[Guest]

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

will report 0 on an empty sheet

 

[Zone]

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

 

[Guest]

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!

 

[Zone]

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

 

[Otto Moehrbach]

Because "CountA" is a worksheet function, you must use "Application" in
front of it. But you can delete the "WorksheetFunction". HTH Otto

 

[Zone]

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]

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]

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

 

[Guest]

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

 

[Guest]

Sorry - I opened my post about mid-thought. On the topic of using
Application vs WorksheetFunction when using XL worksheetfunctions in VBA, I
noticed a small difference between the two using Vlookup.