Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I determine automatically that a sheet is empty?
I want to use a function from the excel COM object to determine that an
excel sheet is empty of any cells, charts, or other elements. Some sheets have no cells but can have other elements like lines or pictures not related to a cell. For instance, say someone wants to reference the COM print for each sheet in a standard excel worksheet, created with the Sheet1, Sheet2, Sheet3 default, but with sheet 1 having some cells, sheet two having just a picture or chart, and sheet three not used. I want to print sheet one and two, but not three. How can the program use the COM object to know that sheet 3 is TOTALLY empty regardless of excel version? Thanks. |
#2
|
|||
|
|||
Some ideas to consider. Not sure how foolproof they are.
1) If either Sheet1.Cells.SpecialCells(xlCellTypeLastCell).Addr ess = "$A$1" or Sheet1.UsedRange.Address = "$A$1" are false then, something is pretty definitely on the sheet. (Don't forget the $ signs since address returns absolute addresses). 2) When the above is True, you would need to check A1 itself to see if it is empty: Len(Sheet1.Range("A1")) = 0 3) UsedRange & xlCellTypeLastCell take into account formatting, etc., but not Pictures or Charts on Worksheets. For that you could use Sheet1.Shapes.Count = 0 There may be some other "gotchas" here (like empty & unused rows being included in UsedRange until deleted, etc.), but if 1, 2 & 3 are all true, I would think you could pretty safely assume that the sheet is blank. (yeah, A1 *could* be empty/valueless but formatted. My approach would simply treat that as an empty sheet. If that's a problem, you'll have some additional work to do. I'm not even sure what you'd check it against. The format of $B$1?) HTH, -- George Nicholson Remove 'Junk' from return address. wrote in message oups.com... I want to use a function from the excel COM object to determine that an excel sheet is empty of any cells, charts, or other elements. Some sheets have no cells but can have other elements like lines or pictures not related to a cell. For instance, say someone wants to reference the COM print for each sheet in a standard excel worksheet, created with the Sheet1, Sheet2, Sheet3 default, but with sheet 1 having some cells, sheet two having just a picture or chart, and sheet three not used. I want to print sheet one and two, but not three. How can the program use the COM object to know that sheet 3 is TOTALLY empty regardless of excel version? Thanks. |
#3
|
|||
|
|||
George Nicholson wrote...
.... 3) UsedRange & xlCellTypeLastCell take into account formatting, etc., but not Pictures or Charts on Worksheets. For that you could use Sheet1.Shapes.Count = 0 Another thing to consider is worksheet-level defined names. Check SomeSheet.Names.Count = 0 Maybe safest to check that the .Count properties of all collection objects that have Worksheet as their parent class are zero. |
#4
|
|||
|
|||
Thanks!
|
#5
|
|||
|
|||
The formula =COUNTA(Sheet1!A1:IV65536) on a different worksheet will return
the count of filled cells on a Sheet1. On 9 May 2005 08:04:24 -0700, wrote: Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I import a website into my excel sheet automatically ever. | Excel Discussion (Misc queries) | |||
How do I stop an Excel sheet from automatically hiding rows when . | Excel Worksheet Functions | |||
in excel, how do i set up a spread sheet that automatically total. | New Users to Excel | |||
Function to automatically insert a new sheet as a result of data entry? | Excel Worksheet Functions | |||
Naming & renaming a sheet tab | Excel Worksheet Functions |