How do I determine automatically that a sheet is empty?

[jgrace5]

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.

 

[George Nicholson]

Some ideas to consider. Not sure how foolproof they are.

1) If either
Sheet1.Cells.SpecialCells(xlCellTypeLastCell).Address = "$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,

 

[Harlan Grove]

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.

 

[jgrace5]

Thanks!

 

[Myrna Larson]

The formula =COUNTA(Sheet1!A1:IV65536) on a different worksheet will return
the count of filled cells on a Sheet1.