Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why is worksheet.usedrange empty? | Excel Worksheet Functions | |||
empty box/worksheet | Excel Discussion (Misc queries) | |||
check if worksheet is empty | Excel Programming | |||
How do I delete an empty worksheet. | Excel Worksheet Functions | |||
XL VBA adding new worksheet if current worksheet is not empty | Excel Programming |