In an Excel worksheet (Office 2004, MacOS X.3.8) with data entered into all the cells from A1 to H8, the following code behaves as one might expect:
tell application “Microsoft Excel”
first row index of (used range of sheet 1 of active workbook)
end tell
==>1
tell application “Microsoft Excel”
first row index of (get end (used range of sheet 1 of active workbook) direction toward the bottom)
end tell
==>8
now, if I select an arbitrary cell or range, the following code returns different result based on the location of the selection.
if selection is outside the used range:
tell application “Microsoft Excel”
set theSelection to (get selection)
set theSelectedRange to item 1 of (get areas of (get properties of theSelection))
set theEndRow to first row index of (get end (get item 1 of (get areas of (get properties of theSelectedRange))) direction toward the bottom)
end tell
==>65536
if selection is contained within the used range:
tell application “Microsoft Excel”
set theSelection to (get selection)
set theSelectedRange to item 1 of (get areas of (get properties of theSelection))
set theEndRow to first row index of (get end (get item 1 of (get areas of (get properties of theSelectedRange))) direction toward the bottom)
end tell
==>8
if selection starts within the used range and extends outside the used range, the script returns 65536. If the selection is an entire row that begins in the used range but extends beyond the right hand boundary of the used range, the script returns 8. If the selection is an entire column, the script hangs in an endless loop.
Now, can anyone explain this behavior? Am I using the ‘get end’ command incorrectly or is this a bug? And can anyone please explain how I can get my script to examine the selection and determine how many rows it spans?
Thanks.
St!ff M!ttens