Re: Detect if Excel 2008 document contains hidden rows
Re: Detect if Excel 2008 document contains hidden rows
- Subject: Re: Detect if Excel 2008 document contains hidden rows
- From: Stan Cleveland <email@hidden>
- Date: Thu, 27 Aug 2009 12:36:12 -0700
- Thread-topic: Detect if Excel 2008 document contains hidden rows
On 8/27/09 3:11 AM, "Bert Groeneveld" wrote:
> Question about Microsoft Excel 2008:
>
> When talking to (the active sheet of) an Excel document: Is there a
> way to detect if the document contains invisible rows, without looping
> through all the rows one by one? Every now and then I meet documents
> with thousands of rows. It would be very time consuming looping
> through all the rows, just to detect if a single one is hidden.
Hi Bert,
The code below will tell you whether or not there are hidden rows, but is
anything but speedy. You'd adjust startRow and endRow to fit the situation,
of course. If you need to know WHICH rows are hidden, then looping is
inevitable, it would seem.
set startRow to 1
set endRow to 100
tell application "Microsoft Excel"
tell active sheet
if (hidden of rows of range (""& startRow &":"& endRow)) contains true then
-- one or more hidden rows
else
-- no hidden rows
end if
end tell
end tell
> Likely the rows are made hidden with the Data->Filter->Auto Filter command.
> Probably there is some command that detects if this option is turned on?
> Hope someone can help, because I am far from an Excel specialist.
Yes, there is a 'filter on' boolean property for each filter that would tell
you when a particular filter is active. However, that doesn't necessarily
guarantee that there are rows hidden as a result (although it's probable).
If you're okay with those odds, it might be a good avenue to explore.
You'd also need to check the 'filter mode' boolean property of the
worksheet, which tells whether filtering is enabled. In summary, if a
sheet's 'filter mode' is true AND some filter's 'filter on' is true, then
it's possible that there are hidden rows.
HTH,
Stan C.
_______________________________________________
Do not post admin requests to the list. They will be ignored.
AppleScript-Users mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
Archives: http://lists.apple.com/archives/applescript-users
This email sent to email@hidden