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: Thomas Maffucci <email@hidden>
- Date: Fri, 28 Aug 2009 00:11:24 -0400
- Thread-topic: Detect if Excel 2008 document contains hidden rows
On 8/27/09, Bert Groeneveld wrote:
>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. 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.
You need more than just one command because autofilter can be turned on but
rows may not be hidden.
This should do the trick:
tell application "Microsoft Excel"
activate
set Schno to 1
set Filtstat to ""
activate object worksheet Schno
set ur to used range of active sheet
set lastCell to (special cells ur type cell type last cell) -- last cell
set collaadr to (get address lastCell reference style R1C1 with column
absolute)
set AppleScript's text item delimiters to {"C"}
set lastcolnum to last text item of collaadr
set AppleScript's text item delimiters to {""}
if autofilter mode of sheet Schno is false then
display dialog "Autofilter is not on all rows are visible"
else
-- "Autofilter is on"
repeat with coldx from 1 to lastcolnum by 1
try
set Filtstat to (filter on of filter coldx of autofilter
object of sheet Schno)
end try
if Filtstat = true then
exit repeat
end if
end repeat
if Filtstat = false then
display dialog "Autofilter is on but all rows are visible"
else
display dialog "Autofilter is on and Rows are hidden"
end if
end if
end tell
Tom
_______________________________________________
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