Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 6th 05, 04:32 PM
 
Posts: n/a
Default 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   Report Post  
Old May 6th 05, 06:10 PM
George Nicholson
 
Posts: n/a
Default

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   Report Post  
Old May 6th 05, 07:18 PM
Harlan Grove
 
Posts: n/a
Default

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   Report Post  
Old May 9th 05, 04:04 PM
 
Posts: n/a
Default

Thanks!

  #5   Report Post  
Old May 9th 05, 08:15 PM
Myrna Larson
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How can I import a website into my excel sheet automatically ever. Mohamed El Hefnawy Excel Discussion (Misc queries) 2 April 13th 05 04:51 PM
How do I stop an Excel sheet from automatically hiding rows when . kazyreed Excel Worksheet Functions 1 February 3rd 05 03:35 PM
in excel, how do i set up a spread sheet that automatically total. jgus New Users to Excel 1 January 14th 05 04:10 AM
Function to automatically insert a new sheet as a result of data entry? Mark Mulik Excel Worksheet Functions 2 November 28th 04 02:21 AM
Naming & renaming a sheet tab Cgbilliar Excel Worksheet Functions 1 November 7th 04 05:57 PM


All times are GMT +1. The time now is 04:06 AM.

Powered by vBulletin® Copyright ©2000 - 2022, Jelsoft Enterprises Ltd.
Copyright 2004-2022 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017